1

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?

Improve this question
0
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)

enter image description here

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.

Improve this answer
4
  • Great tip. Thanks. But the first step will be to change the compatibility level in the first server, yes? Only then I can move them to the new server. is this right? – nunopacheco Nov 18 '16 at 22:46
  • Not necessary... you can move the databases and then change the compatibility level. – Kin Shah Nov 19 '16 at 20:01
  • But the version 2016 does not support level 80.. can I do it anyway? – nunopacheco Nov 20 '16 at 13:34
  • Yes.. just backup and restore.. the db engine will upgrade and then you can change to compatibility 130. – Kin Shah Nov 20 '16 at 15:55
0

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
Improve this answer

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.