August 29, 2016 at 6:12 am
Hi,
In my Prod & Non-Prod, we use a stored proc to pull a list of rows. In non-prod sql server, query completes in 2seconds but in prod it would take 1 min 40sec. Stored proc queries only 3 tables, and they aren't huge. SQL server version,configuration is similar. Validated indexes, keys, fragmentation, last stats updated and they looks good. Execution plan in QA is considering parallelism and had a higher subtree cost(41.456), whereas in prod it is not considering parallelism and have very less subtree cost (3.33). How should i proceed further, please help me on this.
Thanks,
Dan
August 29, 2016 at 6:31 am
dan.brown1 (8/29/2016)
SQL server version,configuration is similar.
That's most likely the issue, similar.
You need to validate that the ANSI connection defaults are the same, same cost threshold for parallelism, same max degree of parallelism, same versions, exactly, of SQL Server, because variations on any of these can lead to differences either in the optimizer itself or in choices made by the optimizer. Especially since the choices made by the optimizer are arriving at one plan going parallel and the other not, it strongly suggests you have differences between the servers, databases, or data. Check the actual statistics using DBCC SHOW_STATISTICS and compare the row estimates between the two databases on the tables and indexes involved. For you to get differences in performance, differences in plans, there are differences in the servers. You just have to find them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 29, 2016 at 6:32 am
dan.brown1 (8/29/2016)
Hi,In my Prod & Non-Prod, we use a stored proc to pull a list of rows. In non-prod sql server, query completes in 2seconds but in prod it would take 1 min 40sec. Stored proc queries only 3 tables, and they aren't huge. SQL server version,configuration is similar. Validated indexes, keys, fragmentation, last stats updated and they looks good. Execution plan in QA is considering parallelism and had a higher subtree cost(41.456), whereas in prod it is not considering parallelism and have very less subtree cost (3.33). How should i proceed further, please help me on this.
Thanks,
Dan
Please post the actual execution plans here as attachments and let others take a look too.
Also, is there anything running in prod at the same time which may be blocking the proc?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 29, 2016 at 8:34 am
Possible causes:
1) Blocking. Use sp_whoisactive to check for this.
2) Review Estimated and Actual rows from the production run. Something is likely causing very low estimates and you are getting index seek/nested loop join plan which is spinning a bajillion logical IOs in prod.
3) Parameter sniffing issue. Crack open the actual execution plan xml and check the parametercompilevalue and see how that compares to the actual execution value for parameter inputs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 29, 2016 at 10:05 am
TheSQLGuru (8/29/2016)
Possible causes:3) Parameter sniffing issue. Crack open the actual execution plan xml and check the parametercompilevalue and see how that compares to the actual execution value for parameter inputs.
Or, much more easily, just look at the properties of the first operator in the plan.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 30, 2016 at 9:08 am
Thanks for your suggestions. Unfortunately i can't provide/share any execution plans as we operate from an secure environment.
Answers to the questions posed
->There are no blocking, CPU and OS levels are at normal.
->Cost threshold for parallelism is set at 5 and Max degree of parallelism is 0 across our SQL estate
->Data differs by some rows from QA(1899729) to Prod(1882943) only in 1 table
->I observed that in QA, in execution plan the clustered index is scanning the entire table,i.e., actual no.of rows is showing as 1899729. But in PROD, it is just 192 rows, which is the final output of SP. I wonder how would optimizer knows the records to output at the first stage.
->In PROD execution plan, Clustered Index scan for all the 3 tables are less than 10% but HASH MATCH (FLOW DISTINCT) is at 74%, where as this doesn't show up in QA execution plan.
What i did
-> Refreshed PROD data onto a Test server
->Rebuild indexes on those 3 tables, updated statistics
->Captured the plan_handle and ran cleaned it up using DBCC FREEPROCCACHE(plan_handle)
->I executed DBCC SHOW_STATISTICS and see that rows sampled in TEST is 86462 and in QA it is 88397
-> Still the SP is taking longer times
Hope this would give some picture and your advise can help me in fixing this.
Thanks,
Dan
August 30, 2016 at 9:30 am
Is the hardware config (CPU/Cores/RAM/Disk Architecture) the same in both environments?
Have you done a side-by-side comparison of the database properties under the 'Options' node in SSMS?
Same question for the properties of the two SQL instances: compare all the nodes there, looking for significant differences.
Someone here probably has a script that can get all that info together for you, but I'm just a lowly developer 🙂
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 30, 2016 at 9:33 am
Grant Fritchey (8/29/2016)
TheSQLGuru (8/29/2016)
Possible causes:3) Parameter sniffing issue. Crack open the actual execution plan xml and check the parametercompilevalue and see how that compares to the actual execution value for parameter inputs.
Or, much more easily, just look at the properties of the first operator in the plan.
A) SQL Sentry's FREE Plan Explorer has the ability to obfuscate plans so you can safely share them.
B) I forgot a key possibility in the different plans: the machine capabilities. You can get VASTLY different plans on big/small hardware/VMs. How far apart is your production and test machine from that perspective (RAM and CPU count especially).
C) Defaulting MAXDOP and CTFP is definitely suboptimal on all modern hardware.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 30, 2016 at 10:24 am
If you're getting different execution plans, then it has to be differences between the servers and/or databases that is causing it. The trick is identifying the differences. Most of the compile information is stored with the plan, lots of it in the first operator. I would compare the two plans properties, ANSI settings, memory grants, etc., it's all listed there. That's going to be your best bet to understand what's happening. It could be the compatibility mode on the databases (it's there in the properties). It's something.
Based on what you've said, you're not backing up & restoring production, but instead are moving around the structures & data. That's still going to leave database settings that could be different.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply