June 30, 2014 at 8:21 pm
I have two servers: one production and one development. There is a third party query that runs on both servers. Yes, the query is poorly tuned - I cannot change it. In production, it runs in 54 minutes. In development, I have tried to let it run for days and it never completes.
Here's what I have tried so far:
Compared the settings of production vs. development. The settings are very similar - the development box is larger, 4 times more memory.
Max degree of parallelism is the same on both boxes.
No compression on both boxes.
The production server is fairly busy, the development server is empty - this is the only process running on it.
Plenty of free disk space.
Updated all statistics on all databases touched by the query on dev.
Indexing is the same on both boxes.
The development box is running MSSQL2012.
The production box is running MSSQL2008R2.
What I've noticed:
The query consumes a massive amount of CPU time.
HUGE number of reads (16 million reads for 10 writes according to sp_WhoIsActive)
Largest wait types are CXPACKET, SOS_SCHEDULER_YIELD and TRACEWRITE respectively.
wait_typesum_wait_time_mspct_wait_timesum_waiting_tasksavg_wait_time_ms
CXPACKET4142765580.7176307423.5
SOS_SCHEDULER_YIELD24146944.7532725530.0
TRACEWRITE19856343.914831338.9
Can you guys point me in the right direction? Give me some ideas of things to research or look at?
Any help is appreciated.
Thanks
July 1, 2014 at 1:54 am
Quick question, is the compatibility level the same for both databases?
😎
July 1, 2014 at 1:55 am
Quick question, is the compatibility level the same for both databases?
😎
July 1, 2014 at 5:13 am
Cost threshold for parallelism on both boxes. ANSI connection settings on both boxes. Get an estimated execution plan from both boxes and compare 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
July 1, 2014 at 6:14 am
Update to the questions:
Production Box - Running MSSQL 2008R2 - compatibility_level = 90
Development Box - Running MSSQL 2012 - compatibility_level = 90
July 1, 2014 at 6:30 am
Cost threshold for parallelism on both boxes. ANSI connection settings on both boxes. Get an estimated execution plan from both boxes and compare them.
Cost threshold for parallelism om both boxes is 5.
ANSI connection settings on both boxes are the same.
I also looked at the CPU's on the boxes since this is a CPU-intense query. The development box is much newer with faster clock speed (2.66) on quad core processors. The production box has dual core processors with 2.0 Ghz.
Looking at execution plans next.
Thank you for all of the suggestions.
July 1, 2014 at 6:50 am
By the way, change that cost threshold value. The default value of 5 is way too low for most systems. I'd suggest bumping it to 50 for an OLTP system or 30 for a reporting system. Test from there to ensure those values work for you.
"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
July 1, 2014 at 8:17 am
Thank you for the suggestion on the Cost Threshold for Parallelism setting. I'm going to change the value and see how the system performs.
There's no doubt that the execution plans differ between the two systems.
The biggest thing that stands out to me is a Stream Aggregate which costs 0% in production and 32% on the DEV box.
Other than that, the index usage is the same, of course the percent cost values of each varies between DEV and PROD.
There is a book out on execution plans that is very next on my list of reading references. I also enjoyed Grant Fritchey's SQL Server Performance lecture at the MSFT campus in Las Colinas last November 1st. I only wish that I had been better prepared and could see the lecture again.
Thank you.
July 1, 2014 at 8:35 am
michael.minarzick 97083 (7/1/2014)
Thank you for the suggestion on the Cost Threshold for Parallelism setting. I'm going to change the value and see how the system performs.There's no doubt that the execution plans differ between the two systems.
The biggest thing that stands out to me is a Stream Aggregate which costs 0% in production and 32% on the DEV box.
Other than that, the index usage is the same, of course the percent cost values of each varies between DEV and PROD.
There is a book out on execution plans that is very next on my list of reading references. I also enjoyed Grant Fritchey's SQL Server Performance lecture at the MSFT campus in Las Colinas last November 1st. I only wish that I had been better prepared and could see the lecture again.
Thank you.
Well, I guess you are talking to the same person who wrote that book :-):-D
July 1, 2014 at 9:16 am
As a bit of a sidebar, I've seen dozens of such posts concerning 2012 and it's not making me real comfortable about 2012.
Rumor has it (I've not done a deep dive on it), there's some sort of setting to change cardinality estimate methods in 2012. Look that up and figure out what the setting is to make it like the old setting and try that on the Dev box.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 1, 2014 at 11:59 am
michael.minarzick 97083 (7/1/2014)
Thank you for the suggestion on the Cost Threshold for Parallelism setting. I'm going to change the value and see how the system performs.There's no doubt that the execution plans differ between the two systems.
The biggest thing that stands out to me is a Stream Aggregate which costs 0% in production and 32% on the DEV box.
Other than that, the index usage is the same, of course the percent cost values of each varies between DEV and PROD.
There is a book out on execution plans that is very next on my list of reading references. I also enjoyed Grant Fritchey's SQL Server Performance lecture at the MSFT campus in Las Colinas last November 1st. I only wish that I had been better prepared and could see the lecture again.
Thank you.
If the plans are different, the indexes referred to may be the same, but I doubt the use is identical. Those estimated costs differences suggest strong differences in the statistics. I know you said you updated them, but you might want to update them again, but make sure you update them with a full scan to see if that makes a difference in the plans. If you want to post the two plans to the forums, I'm sure people here will look them over.
I spoke at Microsoft last November? You might be thinking of someone else. Oh, wait, in Dallas, right. I'm presenting a very similar session at the PASS Summit this year. I'll also be presenting a similar one at Connections in September.
If you're reading the execution plans book, make sure it's the 2nd edition. Fixes problems from the first edition.
"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 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply