MEMCM Console Slow Performance Caused by SQL Compatibility Level

How to fix MEMCM console slow performance where navigating the console, and it takes “forever” to start the console..

I noticed at a customer that when click on the MEMCM icon to start the console, it could literally take a minute or two before the console opened. Working in the console was also very slow.

I verified that Ola Hallengren’s SQL Maintenance Solution where implemented, and indexes where updated. The database sizing where also optimal for the amount of endpoints they where managing.

Check out my previous series:

• SCCM Performance Tuning – How to Fix Slow SCCM – Part 1
• SCCM Performance Tuning – How to Fix Slow SCCM – Part 2

The two most common causes of slow performance in MEMCM are fragmented indexes and or misconfigured database sizing. Since that was not the cause, it it led me down the rabbit hole of troubleshooting to pinpoint the performance issues with the MEMCM console. I eventually looked that the database compatibility level which turned out to be the issue. The SQL queries where performing horrible due to the compatibility level being higher than what Microsoft supports for Configuration Manager.

MEMCM Console Slow Performance Caused by SQL Compatibility Level

The following SQL query shows the compatibility level of your databases.

SELECT name, compatibility_level FROM sys.databases

That query proved that the compatibility level of the MEMCM database was configured to 150, which is the default when creating a new database on an updated SQL Server 2017. MEMCM officially only supports compatibility level 140 and lower, and recommends 140 for maximum performance (110 if you have certain performance issues).

I used the following SQL query to update my MEMCM database to compatibility level 140.

ALTER DATABASE CM_PS1
SET COMPATIBILITY_LEVEL = 140;
GO

And just like that I fixed the MEMCM console slow performance issue that I experienced. The results where immediate. Launching the Configuration Manager console was much quicker, and so was navigating and browsing inside the console. It helps a lot when the database queries execute faster.

Reference: https://docs.microsoft.com/en-us/troubleshoot/mem/configmgr/sql-query-times-out-or-console-slow-performance