MEMCM Console Slow Performance Caused by SQL Compatibility Level

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:

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

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

Anders Rødland

Anders Rødland started his IT career in 2006. My main focus is MS Configuration Manager and client management, and I have passed 17 Microsoft certifications since then. My main expertise is on client management with Microsoft Endpoint Manager: Intune and Configuration Manager. I also do a lot of work on the security side with Microsoft Defender for Endpoint. In addition to my Microsoft certification, I also have an ITIL v3 Foundation certification.This is my private blog and do not represent my employer. I use this to share information that I find useful. Sharing is caring.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.