Upgrade finished? Remember to check the Database Compatibility Level in SQL Server

When you finish upgrading Sitecore from a 8.x to a 9.1 version for example, you might want to restore the upgraded databases into the latest supported SQL Server version according to the Sitecore compatibility table. Once restored, the upgraded databases are still configured to be compatible with the lowest SQL Server version supported, which can decrease the performance of your site.

Before SQL Server 2014, the database compatibility level of databases was not something you should be concerned with from a performance perspective.

After SQL Server 2014, a “new” cardinality estimator (CE) was introduced. The Cardinality Estimator is a SQL Server Query Processor component that is responsible for predicting the number of rows that the query will return.

This estimation of the number of rows in addition to the SQL Server statistics will be used by the SQL Server Query Optimizer to create the optimal and the most accurate execution plan for your query that has the lowest processing cost to execute.

Learn more about cardinality estimator (CE) by reading What’s new in SQL Server 2014 Cardinality Estimator?

Each new version of SQL Server since SQL Server 2014 has CE and query optimizer changes tied to the database compatibility level. So on SQL Server 2016 and newer is CE 120 for compatibility level 120, CE 130 for for compatibility level 130, and so on.

Therefore, it is advisable to set the compatibility level to the latest supported by Sitecore in accordance to the Sitecore compatibility table (see Database Server section).

How is the compatibility level configured

When you create a new database in SQL Server, the database compatibility level will be set to the default compatibility level for that version of SQL Server. e.g. new SQL Server 2017 databases will have a database compatibility level of 140.

If you restore a full database backup that was taken on an older version of SQL Server to an instance that is running a newer version of SQL Server, then the database compatibility level will stay the same as it was on the older version of SQL Server.

If the old database compatibility level is lower than the minimum supported database compatibility level for the newer version of SQL Server, the database compatibility level will be changed to the lowest supported version for the newer version of SQL Server.

To learn more about the database compatibility level in SQL server, read The Importance of Database Compatibility Level in SQL Server.

How to change compatibility level

In the Microsoft SQL Server Management Studio, right click the database and then on the Options section, change the Compatibility level.

To change compatibility level with a query:

ALTER DATABASE AdventureWorks2012  
SET COMPATIBILITY_LEVEL = 120;  
GO

Hope this can increase the performance of your site!

Credits

Photo by Marvin Meyer on Unsplash