|SQL Server version||Supported compatibility level values||Recommended compatibility level for ConfigMgr (applied during installation or ConfigMgr servicing update)||Recommended level for specific performance issues|
|SQL Server 2017||140, 130, 120, 110||140||110|
|SQL Server 2016||130, 120, 110||130||110|
|SQL Server 2014||120, 110||110||110|
To identify what SQL CE compatibility level is in use for the Configuration Manager database, run the following query:
SELECT name, compatibility_level FROM sys.databases
On SQL Server 2014 and SQL Server 2016 RTM, to identify whether using SQL Server 2012 CE (110) may improve Configuration Manager query performance, identify a query that is running slowly and manually test its performance at the SQL 2012 CE compatibility level. To do this, run the query in SQL Server Management Studio with <option (querytraceon 9481)> and compare the execution time to its performance without the flag.
Starting with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT 'FORCE_LEGACY_CARDINALITY_ESTIMATION' query hint instead of using trace flag 9481.
For more information about using querytraceon with trace flag 9481 at the specific-query level, see Enable plan-affecting SQL Server query optimizer behavior that can be controlled by different trace flags on a specific-query level. For SQL Server 2016 SP1 and later versions, see Hints (Transact-SQL) - Query. For information about using SQL Profiler to identify slow queries, see SQL Server Profiler.
See the following example of a specific-query test run at the SQL 2012 CE level against SQL Server 2014:
If the above test indicates that performance gains can be achieved, use the following command in SQL Server Management Studio to set the Configuration Manager database to the SQL Server 2012 CE compatibility level:
SET COMPATIBILITY_LEVEL = 110;
When an SQL Server is upgraded in-place from any earlier version of SQL Server, pre-existing databases will keep their existing compatibility level if they are at the minimum allowed level for that new version of SQL Server. Upgrading SQL Server with a database at a compatibility level lower than the allowed level automatically sets the database to the lowest compatibility level allowed by the new version of SQL Server.
During upgrades or new installations of Configuration Manager, databases are automatically configured to use the recommended SQL CE compatibility version for that version of SQL Server (as shown in the table that is mentioned earlier). Performance improvements are frequently introduced with ConfigMgr servicing updates. If you experience performance degradation after a servicing update, as a result of being reverted back to the default recommended CE level for your version of SQL, reassess whether you may have to manually change the CE level back to 110.
For more information about SQL CE compatibility levels, see ALTER DATABASE Compatibility Level (Transact-SQL).