April 15, 2016 at 4:50 am
Hi
We have restored a sql2008r2 db over to a 2012db. The 2012 db is using the new compatibility level of 110.
We have a stored proc that executes in under 1 second on the 2008r2 db but on the 2012 database it is taking 7 seconds.
When i examine the execution plan, there is a section that has a warning saying 'Type conversion in Expression (CONVERT (archer(20),[xx].[tablename],0) may affect "Cardinality estimate" in query plan choice.
This part of the plan runs parallel on the sql2008r2 box at a level of 24, but because of this warning in the plan it is not going parallel so takes longer on the 2012 box.
Is this because there is a fundamental difference in the optimiser between 2012 and 2008r2 ? In 2014 i know you can run OPTION(QUERYTRACEON 9481) to force the optimiser to use the previous version, so is there a similar trace flag i can turn on to get the optimiser in 2012 to run the 2008r2 or how can i get rid of this warning in the plan - preferably without a code rewrite as we have found other queries that also seem to take longer on the 2012 box.
April 15, 2016 at 7:08 am
There are absolutely optimizer differences between 2008 & 2012. The cardinality estimator is not different. That's what those traceflags in 2014 are for. It's not applicable to 2012 where the cardinality estimator is the same as it was in SQL Server 2000.
You could be seeing a difference because of the optimizer changes. You could be seeing a difference because of server settings. What is the Max Degree of Parallelism on both servers? What is the Cost Threshold for Parallelism on both servers? Was there a change in memory between the servers? Number of CPUs? Any other differences along those lines. Also, have the statistics been updated on the new server? With a full scan? All these things can lead to differences in the execution plan in addition to changes to the optimizer.
"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
April 15, 2016 at 8:17 am
Hi Grant
The settings for both servers are the same in regards
MAX DOP =0
COST THRESHOLD = 5
The faster box (2008r2) has 20Gb capped sql server memory and 24 CPU's
The slower sql2012 box did have the memory set to max (the server has 16Gb installed), so I have changed the sql limit to 10Gb.It only has 4 cpu cores.
I have run an UPDATE STATISTICS WITH FULLSCAN on the database in question but still the same performance in terms of query time.
On the part of the execution plan that runs parallel on the faster box, i see the leftmost SELECT icon gets a memory grant of 400320kb, whereas the slower plan gets a memory grant of only 22216kb and doesn't run in parallel.(plus it has the yellow warning about 'type conversion', although we did manage to rewrite the code to remove this conversion but that section still did not go parallel.
April 15, 2016 at 9:24 am
April 15, 2016 at 9:27 am
What about the row estimates between the servers. Are they the same? You're getting a different memory grant because the optimizer thinks it's moving different amounts of data (and in different ways). It still could be down to statistics, although, there were changes to the optimizer.
"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
April 15, 2016 at 9:50 am
Can you post the execution plans for the query on the two servers? (Actual plan, please, not the estimated - after running with the option to include the actual execution plan turned on, switch to the execution plan tab, right-click and choose save as; then attach the saved .sqlplan files to your post).
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply