6

Recently upgraded from SQL Server 2008 to 2016, been running in compatibility mode 100 for 4 months everything was ticking along nicely (fast). After a fair amount of testing and running the migration adviser i decided to flick the "switch" and changed the compatibility level to 130...

This has had a detrimental effect on a quite a few sprocs/queries , some which where running in split seconds are now taking minutes. Also CPU has bumped up due to this.

These queries are written well, also I rebuild indexes and stats nightly..

Compared plans which look the same, with a couple of percentages slightly off, still the same plan though! i think CE isn't getting good row counts.

Also what is weird some times the queries run fine in 130 so i think all is good, but suddenly get an alert and the query is taken ages just by 2 rows being different then I have to add back OPTION (QUERYTRACEON 9481) to get it speedy again.

Are there any other things I can check / do to help get these queries get back to their former glory?

Should i remove all stored procedures plans from cache???

  • Is the ultimate goal to have them fast or have them fast with new compatibility? – Antoine Hernandez Mar 9 '17 at 17:59
  • Fast with new compatibility – davey Mar 9 '17 at 18:01
  • Did you update your statistics and clear the query plan cache? – Ali Razeghi Mar 9 '17 at 18:33
  • @AliRazeghi I have update stats nightly, haven't cleared the query cache yet though , do think it would be beneficial after reading Joe's answer? – davey Mar 10 '17 at 8:08
  • It's just something I do when I upgrade to a new engine, but sometimes we don't have that luxury and it might be overkill. These things from my experience can happen in multiple layers and the answers provided are excellent to look into. I've ran into issues with bad query plans being created from out of data statistics post upgrade so I figured I'd throw in a quick question for ya but it looks like you're on top of it. – Ali Razeghi Mar 10 '17 at 16:33
9

Microsoft has an upgrade strategy for changing the compatibility mode on SQL Server 2016. Quoting the linked article:

The recommended workflow for upgrading the query processor to the latest version of the code is:

  1. Upgrade a database to SQL Server 2016 without changing the database compatibility level (keep it at prior level)

  2. Enable the query store on the database. For more information about enabling and using the query store, see Monitoring Performance By Using the Query Store.

  3. Wait sufficient time to collect representative data of the workload.

  4. Change the compatibility level of the database to 130

  5. Using SQL Server Management Studio, evaluate if there are performance regressions on specific queries after the compatibility level change

  6. For cases where there are regressions, force the prior plan in the query store.

  7. If there are query plans that fail to force or if performance is still insufficient, consider reverting the compatibility level to the prior setting and then engaging Microsoft Customer Support.

You could try a version of that for your situation. Change the compatibility mode back to 100, enable the query store, go through a full business cycle and get a good baseline, then change compatibility mode and use the query store to analyze poorly running queries and take further action on them.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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