3

So yesterday I posted this question regarding that we have upgraded from SQL Server 2008 to SQL Server 2017 and that we excperience some performance problems.

I got the answer to try updating statistics which worked for one database, but not the others. Should I set compability level from 2008 to 2017? At the current time, compatibility level has been left at 100 (SQL Server 2008).

Old server was a physical server, the new server is a VM (Hyper-V).

After the upgrade the execution plan looked different from the old one and it seems to use Index Spool (Eager Spool) instead of using index. Which means that execution time went from 3 seconds on the old server to 1 minute 50 seconds on the new server.

The only thing I notice that is different from the database that does not have Eager Spool with same query is that the table rows are less, it's 13.000.000 rows on the non-working vs 70.000 rows on the working with correct execution plan.

The plans below are anonymized but I hope there's enough information.

Here is the plan with Eager Spool vs The other databas with Index

I have tried

  • Updating statistics
  • Changing cost threshold for parallelism and max degree of parallelism accordingly.
  • Double check indexes if they're as before.
  • I've looked at READ_COMMITTED_SNAPSHOT.
  • I've checked the NUMA configuration.

Another problem is that wait types are high 

LCK_M_IS, 
CXPACKET, 
LCK_M_X, 
ASYNC_IO_COMPLETION, 
LCK_M_IX, 
PAGELATCH_EX, 
BACKBUFFER, 
TRACEWRITE.

I am starting to run out of ideas.

Max memory is set to leave 4GB for OS, I noticed yesterday that power plan on the host machine was set to balanced but changed it to high perf, but still same problem.

On received advice to update statistics:
Seems to have worked on one of the databases, however I still have a problem on all the other databases.

The old server was a physical server with 8 cores and 32GB RAM. The new server is a VM running on Hyper-V with 4 cores and 64GB Ram.

|improve this question
  • 2
    A quick test might be to temporarily add the trace flag to disable the new cardinality estimation. At least it could give you an idea of where the problem is. – Jacob H Oct 10 '18 at 15:14
  • 1
    One database is in production the rest are testing – Enigma Oct 11 '18 at 13:05
  • It looks like on your faster DB it's doing a clustered index seek vs a scan on the slower one, which is essentially a table scan. How many indexes do you have on this table and are they all used? Other than rewriting the query, have you looked into changing the clustered index key? – Alen Oct 11 '18 at 15:20
  • @Enigma - did you figure out where the problem was? – Max Vernon Oct 15 '18 at 14:27
  • 1
    No, I have to do it at night time since it's production. I will try your answer tonight. @Alen There are 6 indexes on the table, both on the new server and old server. I have not – Enigma Oct 15 '18 at 14:59
3

It's possible having double the amount of RAM in the new server is causing the query optimizer to choose a plan that performs worse than expected.

You could try using DBCC OPTIMIZER_WHATIF1 to see what happens if you have 20 cores and only 32 GB of RAM, like the old system. I wrote a blog post showing how that works on sqlserverscience.com.

To see the effect on plan choice with 32GB of RAM, you could use this command:

dbcc optimizer_whatif ('MemoryMBs', 32768);

You'll need to evict the plan for the query from the plan cache, or force a new plan to be compiled using WITH RECOMPILE or OPTION (RECOMPILE) to see if that causes SQL Server to choose the "better" plan.

If you see a better plan after using dbcc optimizer_whatif, you might consider either re-factoring the query to allow the optimizer to make a better choice, or you could create a plan guide.

You may be interested in this question, and the answers about the differences in execution plans on two servers with different resources.

FYI for future visitors: the question states that most of the databases have been kept at compatibility level 100; this rules out an array of potential issues related to cardinality estimator changes in SQL Server 2014+.

1 - be aware, this is an unsupported DBCC command, and should only be used for testing purposes.

|improve this answer
  • So I tested changing the memory and cores accordingly to the old server. I also updated statistics and flushed plan cache. However no success there neither unfortunally – Enigma Oct 16 '18 at 2:16
  • There was no change in the plan at all? – Max Vernon Oct 16 '18 at 2:17
  • 1
    Hmmm... very strange. It's hard to say what is wrong without being there. – Max Vernon Oct 16 '18 at 2:21
  • 1
    Yes indeed.. I were thinking it could be that the user didn't have permissions to the statistics, but I could access them, so that's not the problem neither. – Enigma Oct 16 '18 at 2:30
  • 1
    You could try dropping the memory down to, say 4GB, just as a test to see if that has an effect. – Max Vernon Oct 16 '18 at 2:40
2

When upgrading SQL Server there are a number of tasks that should be completed, to ensure a "smooth"(er) transition.

A list that I use can be found here. This list includes tasks like running CHECKDB, checking compatibility levels, refreshing views, and updating statistics.

In your case, stale statistics appears to have been your problem. The result of this is that the cardinality estimator is unable to make accurate estimations for predicting data distribution and rows returned, and thus produces a different and potentially sub-optimal execution plan.

Edit based on updated question

You also mention about updating the Compatibility Level. The above links provide information with regards to how, and why the Compatibility Level should or could be adjusted. One of the main reasons for changing it is that v12 (SQL Server 2014) provided a major upgrade in the Cardinality Estimator. The best way to determine if this will make a difference for you is to test it, preferably before your upgrade has gone live. This may help you in generating similar execution plans, post-upgrade.

|improve this answer

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.