Change compatibility Level on 80 to 100

Question

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:

1. Should I expect differences in performance after the
   compatibility level change?

2. What is the best way to measure the performance before and after?

3. Should I take special care when moving the databases the SQL
   server 2016?

Answer 1

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?

I have written lenghty answer for migrating from lower version of sql server to higher version of sql server.

Answer 2

Per Books Online, compatibility level sets certain database behaviors to be compatible with the specified version of SQL Server. As such, some queries may no longer work or may need to be slightly changed. These may be performance related especially when going to level 120 or higher as those change how the query optimizer works. If you do not change compatibility level before upgrade, the upgrade process will upgrade to lowest level compatibility on the new install, in this case to 100. The benefit of changing compatibility first before migration is to see if any queries need modification (assuming an application is attached to this database).

Since this is a migration, one way to test performance is to migrate the databases to the 2016 server and then run same queries on both and gather metrics from them to compare. However, on same server performance should be similar, assuming the query is still able to run. An example of a query that will no longer run after changing compatibility mode would be as follows:

SELECT table1.column1
    , table2.column2
FROM table1,table2
WHERE table1.column1 *= table2.column1