SQL Server Upgrade 2008 to 2016 – Compatibility Slow Queries

sql serversql-server-2016

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???

Best Answer

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.

Related Question