Considerations before changing Compatibility Level in SQL Server

You’ve done an upgrade on your mission critical database thinking that the latest version/release would help you overcome all the problems in world, and then realize that there’s a huge performance dip when compared between the older and newer version of your SQL Server Databases. Queries which used to run lightening fast on your lower version are now taking minutes and this can be a frustrating experience for your customers, business teams.

Note that with every database upgrade, compatibility level changes – for example, with SQL 2008 the Compatibility Level was set to 100; in SQL 2012 to 110; in SQL 2014 it’s set to 120; and today, with SQL Server 2016 Compatibility Level to 130.

Although most workloads would benefit from the upgraded Compatibility Level, a few considerations before changing ‘that’ compatibility level in your SQL Server database.

  1. You move to the new Compatibility Level post upgrade, and see how things perform. In case you notice some regressions, you just simply set the Compatibility Level back to its original level, or keep the upgraded one, and only reverse the Cardinality Estimates back to the legacy mode.
  2. If you have multi-tenant applications spanning across multiple databases, it may be necessary to update the provisioning logic of your databases to ensure a consistent Compatibility Level across all databases; old and newly provisioned ones. Your application workload performance could be sensitive to the fact that some databases are running at different Compatibility Levels, and therefore, Compatibility Level consistency across any database could be required in order to provide the same experience to your customers all across the board. Note that it is not a mandate, it really depends on how your application is affected by the Compatibility Level.
  3. Regarding the Cardinality Estimates, and just like changing the Compatibility Level, before proceeding in production, it is recommended to test your production workload under the new conditions to determine if your application benefits from the Cardinality Estimates improvements.
  4. There are improvements with every upgraded version of SQL Server but if for some reason your workload is not tested fully with the higher version and giving you performance headaches, you should think of using “Alter Database” command, change it’s compatibility level and see the behavior.

Hope this helps!

Happy learning!

Cheers!
Aman Kharbanda