SQL Server – Configuration Manager Console Running Very Slow and Time Out

A Compatibility Level change helped one customer’s Configuration Manager Console. I keep that incident separate from a universal repair recommendation.

During the health check, our first tuning changes improved the workload by about 10 percent. That was less than I expected, and I was concerned that the engagement was not delivering enough value. The database was still at compatibility level 110.

SELECT name, compatibility_level
FROM sys.databases
WHERE name = DB_NAME();
-- Historical SQL Server 2016 case, not a recommendation for every product:
-- ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL = 130;

Moving that SQL Server 2016 database from level 110 to 130 brought a substantial improvement. Queries that timed out completed quickly. We didn’t retain a controlled benchmark proving compatibility alone caused every improvement. That limits what I can claim from this incident.

First identify the product and its database role. Microsoft Configuration Manager differs from SQL Server Configuration Manager. Check the product’s support requirements and capture a workload baseline. I test the proposed compatibility level on a restored copy before changing production.

Compatibility can change optimizer behavior and can also introduce regressions. Keep Query Store evidence and a supported rollback level available rather than treating the highest level as an automatic solution.

Reference: Compatibility-level changes and query behavior.

Related reading

• Comprehensive Database Performance Health Check
• change the compatibility level for SQL Server

A successful customer change is not a universal fix, it is an observation to test against the next workload.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.