Supported SQL Server database compatibility level for SharePoint Server 2016 installations

Applies to: SQL Server 2017Microsoft SQL Server 2016SharePoint Server 2016

Symptom


When you use SharePoint Server 2016 together with Microsoft SQL Server version 2016 or 2017, you may notice decreased query performance or increased CPU usage on the database server.

Cause


This issue happens because content databases that are created by SharePoint Server 2016 use the default database compatibility level for the version of SQL Server that the database is installed on. For example, if the SharePoint databases are deployed in an instance of SQL Server 2016, the databases are set to the 130 database compatibility level. Similarly, in an instance of SQL Server 2017, the databases are set to the 140 database compatibility level.

Workaround


SharePoint Server 2016 content databases that are deployed on SQL Server versions 2016 and 2017 are tested and validated to work best with compatibility level 110. Therefore, we strongly recommend that you set the database compatibility level to 110 for SharePoint Server 2016 content databases. To change the compatibility level, run the following TSQL command:

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 110

You can view the compatibility level of all the databases in an instance of SQL Server by using the following TSQL query:

SELECT name, compatibility_level FROM sys.databases

Status


Microsoft is investigating this issue and will update this article when more information and guidance is available.

More Information


The following table shows the supported database compatibility levels to use for different versions of SharePoint Server.

Server version

Supported database compatibility level

SharePoint Server 2016

110

SharePoint Server 2019

130


For more information about database compatibility during version upgrades
and a list of default and supported database compatibility levels for each version of SQL Server, see ALTER DATABASE (Transact-SQL) compatibility level on the Microsoft Docs website.