tag:blogger.com,1999:blog-264303844684390564.post2586831889784726940..comments2022-09-06T01:24:35.424-07:00Comments on iLearn: SQL QUERY NIGHTMARELokeshhttp://www.blogger.com/profile/07208348963993174042noreply@blogger.comBlogger15125tag:blogger.com,1999:blog-264303844684390564.post-38516256654059070402018-05-08T07:04:21.262-07:002018-05-08T07:04:21.262-07:00I appreciate your work on Sql Server It's suc...I appreciate your work on Sql Server It's such a wonderful read on MSBI course. Keep sharing stuffs like this. I am also educating people on similar MSBI training so if you are interested to know more you can watch this MSBI tutorial:-<br /><br />https://www.youtube.com/watch?v=tFG-VkaSvhI<br /><br />and regarding the Certification Process and step by step instructions please check on this LInk :-<br /><br />https://www.youtube.com/watch?v=yf__UkGxQ8c<br /><br />and if you are a beginner in MSBI then its important to get started from the scratch so check this link for better understanding:-<br /><br />https://www.youtube.com/watch?v=OzmdY0zCw4g<br />https://www.youtube.com/watch?v=EdF9tZliIokAnonymoushttps://www.blogger.com/profile/12197351816031442897noreply@blogger.comtag:blogger.com,1999:blog-264303844684390564.post-82616018041425756952016-10-10T08:33:23.272-07:002016-10-10T08:33:23.272-07:00I would ask the dba to check on that. ThanksI would ask the dba to check on that. ThanksLokeshhttps://www.blogger.com/profile/07208348963993174042noreply@blogger.comtag:blogger.com,1999:blog-264303844684390564.post-84246709294992551862016-10-10T08:32:05.680-07:002016-10-10T08:32:05.680-07:00Plans were identical.
ThanksPlans were identical.<br /><br />ThanksLokeshhttps://www.blogger.com/profile/07208348963993174042noreply@blogger.comtag:blogger.com,1999:blog-264303844684390564.post-61352466735874787962016-10-09T14:18:42.371-07:002016-10-09T14:18:42.371-07:00Have you compared execution plans between 2 enviro...Have you compared execution plans between 2 environments? Any differences?<br />Something as simple as disk fragmentation could cause the differences, or how many concurrent processes are running on both environments.Anonymoushttps://www.blogger.com/profile/04929798160842812651noreply@blogger.comtag:blogger.com,1999:blog-264303844684390564.post-27210776182192919602016-10-08T08:11:21.026-07:002016-10-08T08:11:21.026-07:00Are the server specs the same?
How about the max p...Are the server specs the same?<br />How about the max parallelism?<br />Any other settings on SQL different between servers?<br />Any resource governor in place on live?Anonymoushttps://www.blogger.com/profile/00425862479446408149noreply@blogger.comtag:blogger.com,1999:blog-264303844684390564.post-13882386440416490732016-10-07T22:40:10.110-07:002016-10-07T22:40:10.110-07:00Thanks Marccelo, I'll look into this!Thanks Marccelo, I'll look into this!Lokeshhttps://www.blogger.com/profile/07208348963993174042noreply@blogger.comtag:blogger.com,1999:blog-264303844684390564.post-28610823326297638732016-10-07T22:39:35.021-07:002016-10-07T22:39:35.021-07:00Thanks for the detailed explanation. As I explaine...Thanks for the detailed explanation. As I explained in the description the data in the dev and prod databases is almost the same.<br /><br />All I want to see why a particular stored proc runs for 5 minutes in dev however takes ages to run in prod even though nothing has changed in the prod and the same code was running without any problems since so long.Lokeshhttps://www.blogger.com/profile/07208348963993174042noreply@blogger.comtag:blogger.com,1999:blog-264303844684390564.post-66627993607007156732016-10-07T10:53:37.397-07:002016-10-07T10:53:37.397-07:00Another difference that could have in production e...Another difference that could have in production environment and developer, is the traceflag's.<br /><br />In case of difference on traceflag 4199 I suggest you to see if the version of SQL Server was the same in both and all hotfixes was installed. This leads you to different plans.<br /><br />Another shot could be sp_configure: e.g. cost threshold for parallelism<br /><br />And the last, connection options: Ansi_nulls, Ansi_Warnings..Anonymoushttps://www.blogger.com/profile/03184527248735775489noreply@blogger.comtag:blogger.com,1999:blog-264303844684390564.post-52010190298473473752016-10-07T07:46:50.890-07:002016-10-07T07:46:50.890-07:00I counted a lot scans:
> Outer Joins.
> A fu...I counted a lot scans:<br />> Outer Joins.<br />> A function call in a WHERE clause.<br />> Searches on greater than, less than. Note that all inequalities will always cause scans.<br /><br />I don't have the time to dissect your query but you can assume that a big query with a lot of scans will take some time to execute and consume much if not most of the RAM on your server. <br /><br />I think you can further improve the performance of your revised query.<br /><br />I recommend:<br />> Break the query into smaller pieces. <br />> Use the sub-queries to populate Temp tables before you execute the main query. In other words, take the sub-queries our of the main query. Join on the temp tables in the main query. Sometimes, you can use Table variables for this. There are rules that matter with Table variables; I don't have the time to cover them here. Used improperly, Table variables cause scans.<br />> Try to eliminate outer joins wherever possible.<br />> Try to eliminate any data comparison that is not an equality. <br />> Eliminate all function calls from WHERE, ON, and HAVING statements.<br />> Think about every component of your query and how many locks it must hold to complete. Do everything you can to reduce the number of locks held and their duration.<br />> Get rid of the DISTINCT statement if you can. It causes additional work and execution time.<br /><br />One last thing. When databases are small and servers are not under load, every query tends to execute quickly. My guess is this might describe your test server. <br /><br />The production server is likely a very different environment. The database(s) are likely much bigger. It's a contentious environment where queries are competing for server resources and access to the same data. Of course, it's going to take longer to execute than it did on a test server.<br /><br />In the production environment, you want to minimize the locking and blocking times by dividing your query up into smaller segments that "nibble" at the database rather than take one large bite. Then, at the end, you can assemble the desired result by using one large query that JOINS on your temp tables. They're your temp tables so your query will not block other queries, nor can it be blocked.<br /><br />Hope this helps.A Concerned Citizenhttps://www.blogger.com/profile/09874500248871378053noreply@blogger.comtag:blogger.com,1999:blog-264303844684390564.post-48436892387936702702016-10-07T07:21:57.608-07:002016-10-07T07:21:57.608-07:001. Old query
2. They were identically the same. I...1. Old query<br /><br />2. They were identically the same. In prod cpu io went to 100% which caused the stored proc to run very slow and you eventually have to terminate the execution. Whereas in dev io too reached 100 % but momentarily. <br /><br />3. Didnt check that would do tommorow, whats the impact for this please?<br /><br />4. its a transactional data. prod has a bit more data than dev, however not essentially the sameLokeshhttps://www.blogger.com/profile/07208348963993174042noreply@blogger.comtag:blogger.com,1999:blog-264303844684390564.post-5111429217416794902016-10-07T07:18:02.728-07:002016-10-07T07:18:02.728-07:00Hi Jon. There were no input parameters so that rul...Hi Jon. There were no input parameters so that ruled it out.Lokeshhttps://www.blogger.com/profile/07208348963993174042noreply@blogger.comtag:blogger.com,1999:blog-264303844684390564.post-32911462802830155132016-10-07T07:16:11.960-07:002016-10-07T07:16:11.960-07:00Hi Benett....thanks for your comment. I was able t...Hi Benett....thanks for your comment. I was able to fine tune the query as you can see in the description. My main reason for writing this blog was to find out what was the issue. Coming back to your suggestion, development database has the stats refresh every week whereas in prod its on daily basis.<br /><br />Didnt try rebuilding the indexes though.Lokeshhttps://www.blogger.com/profile/07208348963993174042noreply@blogger.comtag:blogger.com,1999:blog-264303844684390564.post-61566515362122058342016-10-07T06:40:08.412-07:002016-10-07T06:40:08.412-07:00Is this a new query in Prod or has it run fine bef...Is this a new query in Prod or has it run fine before?<br /><br />Have you compared the query plans for Prod and Development, what about the io statistics for the query?<br /><br />Have you checked that the prod and development databases are in the same compatibility mode / using the same cardinality estimates. <br /><br />You say that the databases are about the same size but is the data similar? Joe Mhttps://www.blogger.com/profile/16806114340712311114noreply@blogger.comtag:blogger.com,1999:blog-264303844684390564.post-65061675690805705202016-10-07T05:17:01.236-07:002016-10-07T05:17:01.236-07:00Is parameter sniffing a possible culprit?Is parameter sniffing a possible culprit?Jonathan Bhttps://www.blogger.com/profile/11767762437693262197noreply@blogger.comtag:blogger.com,1999:blog-264303844684390564.post-82019785461091831552016-10-07T05:07:45.664-07:002016-10-07T05:07:45.664-07:00Hi, First in comparing test to production, are th...Hi, First in comparing test to production, are the databases similar in size? <br /><br />Usually, when identical databases perform so drastically different for the same query, I would suspect that indexing and statistics related to those indexes are probably in need of an update. With a rebuild of all indexes and fresh statistics, let us know how your database query is performing.<br /><br />Good luck.<br />Jeff Bennett<br />Missouri, USJBhttps://www.blogger.com/profile/00496215533313684221noreply@blogger.com