We are about move all databases from a SQL server 2008r2 (Standard edition) to a 2016 standard edition server.
Many databases have its compatibility level set at 80, wich is not supported on the 2016 server. Our plan is to change all databases to level 100, run some tests, and only then, move the databases to the new server.
My questions are the following:
-
Should I expect differences in performance after the
compatibility level change? -
What is the best way to measure the performance before and after?
-
Should I take special care when moving the databases the SQL
server 2016?
Best Answer
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)
Its a big change for your application. SO test it properly. You can use Database Experimentation Assistant (New & in preview)
You can use Query store (new in sql server 2016) to measure performance and find easily regressed queries interms of IO, CPU, etc.
I have written lenghty answer for migrating from lower version of sql server to higher version of sql server.