October 16, 2014 at 4:20 pm
I have two servers with exact same hardware configuration. One runs SQL 2014 and one SQL 2008R2, both Standard Edition.
They each house a copy of the same database.
I run the exact same query against both, and 2008R2 completes it significantly faster.
When I look at the execution plan, it looks the same on both servers... except on 2014, there's a Sort that has a warning "Operator used tempdb to spill data during execution with spill level 1". I'm pretty sure that is exactly what is killing my performance.
I know that spills usually indicate problems with indexes or statistics. So, I rebuilt the indexes and updated statistics. Estimated number of rows and memory grant are the same on both servers. Yet 2014 continues to spill no matter what I do.
I compared every setting I could think of on both servers. I made sure that 2014 has ACRES of memory available to it. I have read tons of articles on the subject at this point, and they all boil down to stats and indexes. Can anyone help me with this? I'm reasonably sure this isn't the problem with the query or the DB schema, as they are identical on both servers. But something is hamstringing 2014...
October 16, 2014 at 4:33 pm
Bear in mind that in 2008 Management studio didn't show the spill to TempDB warning. Only from 2012 is that warning shown.
Pull out Profiler, trace for hash and sort spills and double-check that the spills are different on the two and that it's not just that 2014 is showing you more information.
Can you post execution plans?
Is the DB on the 2014 instance set to compat mode 110?
p.s. Sorts are very prone to spilling because they require lots of workspace memory, often more than would be possible for the query to get. It's not the buffer pool we're talking about here, it's working memory for the query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 16, 2014 at 4:48 pm
Database was set to compatibility level 100. Could you tell me what impact changing it to 110 would make?
Will post execution plans tomorrow. Thanks for the help so far.
October 16, 2014 at 4:54 pm
It will enable the new cardinality estimator, which could easily have caused what you see, if the DB had been on compat mode 110. Since it's not, we can eliminate that as a possible cause.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 17, 2014 at 3:44 am
Also remember that changes in the optimizer from one version of SQL Server to the next can result in different query behavior. Most of the time you only see issues on queries that were already edge cases for performance and behavior. But these regressions can be painful at times. Usually it requires some modifications to the code. Sometimes changes to the structure.
"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
October 17, 2014 at 7:56 am
Have you checked the power options on them both? If one is set to Balanced then this could make a difference in execution time, it should be High Performance.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply