OpsMgr 2012 R2: MonitoringHost.exe causes 100% or high CPU usage on Windows 2008 SP2 or R2 servers after importing SQL MP 6.6.4.0 or 6.6.2.0

Update (April 8th ) -  the SQL team has recently released a community technical preview, where the fix for this issue is included. Have a look at this blog post (download link there): https://blogs.msdn.microsoft.com/sqlreleaseservices/system-center-management-pack-for-sql-product-family-community-technology-preview-ctp2/

Hi all,

We're seeing more and more reported issues of the monitoringhost process taking up to 100% of the CPU (or a really high usage on machines with more than 4 cores) on servers running Windows 2008 SP2 or Windows 2008 R2 SP1, after importing SQL MP 6.6.4.0 or 6.6.2.0.

After collecting and analyzing several memory dumps of the process while the issue was occurring, we pinpointed the issue on two of the scripts from the SQL Management Pack (CPUUsagePercentDataSource.ps1 and DBDiskLatencyDataSource.ps1) that are causing the bellow .NET issue, due to a recent change on those same scripts:

High CPU in .NET app using a static Generic.Dictionary | If broken it is, fix it you should - https://blogs.msdn.microsoft.com/tess/2009/12/21/high-cpu-in-net-app-using-a-static-generic-dictionary/

To mitigate the issue, disable the following rules and monitors on the SQL 2008 MP, if that server is running SQL 2008:
Rules:
Microsoft.SQLServer.2008.DBEngine.CPUUsagePercent.Collection - MSSQL 2008: Collect DB Engine CPU Utilization (%)
Microsoft.SQLServer.2008.DBEngine.ThreadCount.Collection - MSSQL 2008: Collect DB Engine Thread Count
Microsoft.SQLServer.2008.Database.DiskReadLatency.Collection - MSSQL 2008: Collect DB Disk Read Latency (ms)
Microsoft.SQLServer.2008.Database.DiskWriteLatency.Collection - MSSQL 2008: Collect DB Disk Write Latency (ms)

Monitors:
Microsoft.SQLServer.2008.DBEngine.CPUUsagePercentMonitor: CPU Utilization (%)
Microsoft.SQLServer.2008.DBEngine.ThreadCountMonitor: Thread Count
Microsoft.SQLServer.2008.Database.DiskReadLatencyMonitor: Disk Read Latency
Microsoft.SQLServer.2008.Database.DiskWriteLatencyMonitor: Disk Write Latency

If the server is running SQL 2012, please disable the following rules and monitors:
Rules:
Microsoft.SQLServer.2012.Database.DiskReadLatency.Collection - MSSQL 2012: Collect DB Disk Read Latency (ms)
Microsoft.SQLServer.2012.Database.DiskWriteLatency.Collection - MSSQL 2012: Collect DB Disk Write Latency (ms)
Microsoft.SQLServer.2012.DBEngine.CPUUsagePercent.Collection - MSSQL 2012: Collect DB Engine CPU Utilization (%)
Microsoft.SQLServer.2012.DBEngine.ThreadCount.Collection - MSSQL 2012: Collect DB Engine Thread Count

Monitors:
Microsoft.SQLServer.2012.Database.DiskReadLatencyMonitor - Disk Read Latency
Microsoft.SQLServer.2012.Database.DiskWriteLatencyMonitor - Disk Write Latency
Microsoft.SQLServer.2012.DBEngine.ThreadCountMonitor - Thread Count
Microsoft.SQLServer.2012.DBEngine.CPUUsagePercentMonitor - CPU Utilization (%)  

In the meantime and since the issue is related with PowerShell, I’ve recommended some of my customers to upgrade it to the latest version (4.0) and since then, they didn’t report the issue back (even after enabling the rules and monitors again):
Download Windows Management Framework 4.0 from Official Microsoft Download Center - https://www.microsoft.com/en-gb/download/details.aspx?id=40855

The SQL Management Pack engineering team already made a fix for this, which is to force these scripts to run in PowerShell 2.0 context: the fix will be included in the next public release of the SQL MP.

Hope this helps!

Best regards,

José Miguel Constantino  
Escalation EngineerGlobal Business SupportEMEA Customer Service & Support