MECM slow / SQL maybe the issue...

Question

Hello,

I'm noticing that it takes a while for MECM to respond to anything I do within the console. Doing some googling, it might be a SQL performance due to compatibility level.

Running a SQL query:
SELECT name, compatibility_level FROM sys.databases:
This is what the result are

master 140
tempdb 140
model 140
msdb 140
SUSDB 100
CM_EW0 140
ReportServer 140
ReportServerTempDB 140

Note that I'm running a SQL server version 2017.140.4321.10. Looking at the compatibility list resolution (https://learn.microsoft.com/en-us/troubleshoot/mem/configmgr/sql-query-times-out-or-console-slow-performance), I'm at the recommended compatibility level of. I tried to set it for 110 via SQL query to update the level to 110

ALTER DATABASE CM_PS1
SET COMPATIBILITY_LEVEL = 110;
GO

But I get the error of:

Msg 5011, Level 14, State 5, Line 1
User does not have permission to alter database 'CM_PS1', the database does not exist, or the database is not in a state that allows access checks.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

I'm not sure on how to read this...?

I did change the UseLegacyCardinality value from 1 to 0 which improve the performance a little, no longer getting sluggish response from the configuration manager console.

Any idea's how I might improve the performance of MECM?

Thanks in advance.

Answer 1

CompatabilityLevel is very rarely the issue. (maybe it is for you, but it is rare).

Is your database actually called cm_PS1? or was that just the query you found, and presumed that everyone's CM database is cm_ps1 (it may not be, for you)?

If your DB is called cm_ps1...then that result for alter database means you don't have rights to do that on that DB. (which is odd, since you were able to change legacycardinality). Based on what you posted, your DB is actually cm_ew0 ?

---------
I suggest you do this... if you haven't already done so. Basically, if you are not already doing the Ola Hollangren routine... you are missing out, and desperately need that set up, yesterday if not sooner than yesterday!

https://msendpointmgr.com/2017/01/03/configuration-manager-sql-database-maintenance/

and (possibly / probably / maybe) this:
https://emptygarden.info/2018/03/the-very-best-configmgr-database-maintenance-plan-in-the-entire-world/

Answer 2

There are few things you need to consider and change from default, like memory settings in SQL instance. Go through this stuff or some similar SQL best practise around CM topic. I remember from my old days, that RAM memory allocation is very important, basically 32gb ram is recommended minimum, and you need to give some of that to WSUS as well. So make sure your setup is based on best practise / recommendations.

https://www.systemcenterdudes.com/sccm-best-practices/

Answer 3

@Sherry Kissinger , cm_ps1 is not the database. I don't even know where it collected that information.

...
I'll download the 'MaintenanceSolution.sql' opened the script as a new query. I then changed the line DECLARE @BackupDirectory nvarchar(max) = 'X:\MaintenanceSQLBackup', specified backup folder and executed it. I don't think it worked since there were to files within the X:\MaintenanceSQLBackup' folder. Anything I'm missing?