I plan to change the compatibility level on the databases in an sql server 2016 sp1 instance from 100 to 130. When upgraded from sql 2008 to sql 2016 the compatibility level was changed from 80 to 100.
I also am in the planning stages of installing the SP2 and the latest cumulative update.
What is best practice for changing the compatibility level – before or after the SP2 and cumulative updates are installed?
Sql-server – alter compatibility level
sql-server-2016
Related Solutions
The major difference between sql server 2012 and below vs sql server 2014 and up is the new cardinality estimator (CE)
See my answer here as well.
My Story
When we migrated from sql server 2008R2 to 2014, we saw huge query regressions happening on our application. So we decided to enable TF 9481 but still have the latest compatiblity level.
Upgrading from sql server 2014 to 2016, I did not see any query regressions. So no Traceflag or older compatiblity mode required in sql server 2016.
In sql server 2016, there is database scope configuration option to govern the CE and optimizer fixes (equivalent of TF 4199)
Should I expect differences in performance after the compatibility level change?
Its a big change for your application. SO test it properly. You can use Database Experimentation Assistant (New & in preview)
What is the best way to measure the performance before and after?
You can use Query store (new in sql server 2016) to measure performance and find easily regressed queries interms of IO, CPU, etc.
Should I take special care when moving the databases the SQL server 2016?
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:
Upgrade a database to SQL Server 2016 without changing the database compatibility level (keep it at prior level)
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.
Wait sufficient time to collect representative data of the workload.
Change the compatibility level of the database to 130
Using SQL Server Management Studio, evaluate if there are performance regressions on specific queries after the compatibility level change
For cases where there are regressions, force the prior plan in the query store.
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
- Sql-server – Do SQL service packs effect SQL Compatibility mode
- Sql-server – why will compatibility mode change amount of storage iops
- Sql-server – SQL Server 2016 Compatibility Level and Datetime 2
- Sql-server – Difference between changed compatibility level database and new database
- Sql-server – Cannot set up replication between two servers
- Sql-server – Changing compatibility level from 2008 to 2016
- Sql-server – Compatibility level of databases based on sql server version
- Sql-server – High CPU Usage on certain query when changing DB Compatibility Level
Best Answer
SQL Server 2016 SP1 is out-of-support so you should upgrade to supported service pack first.
Then the best practices for altering the database compatibility level are documented here: Change the Database Compatibility Level and use the Query Store