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 Engineer
Global Business Support
EMEA Customer Service & Support 
  
 

 

 

Comments (12)

  1. Michiel says:

    José, as I'm reading through the list of fixes of the CTP pack, I can't really find an fix for 100% cpu usage or something related to high usage, monitoringhost.exe or other possible related workload fixes. Same goes for the CTP MP guides. Did you already
    test this CTP version?

  2. Hi Michiel, in fact the post and MP Guide doesn’t mention this fix, but I’ve double checked the MPs (2008, 2012 and 2014) and the fix is there. The fix is to explicitly invoke PowerShell 2 while running the affected scripts:

    powershell.exe -version 2 -NoLogo -NoProfile -Noninteractive "$ep = get-executionpolicy; if ($ep -gt ‘RemoteSigned’) {set-executionpolicy -Scope Process remotesigned} & ‘$$file/CPUUsagePercentDataSource.ps1$$’ -computerName ‘$Config/ComputerName$’ -SQL_WMI_NAMESPACE
    ‘ComputerManagement’ -serviceName ‘$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ServiceName$’ -sqlInstanceName ‘$Target/Property[Type="SQL!Microsoft.SQLServer.ServerRole"]/InstanceName$’ -connectionString ‘$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/ConnectionString$’
    -tcpPort ‘$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/TcpPort$’ -instanceId ‘$Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/InstanceID$’

    As I mentioned above, some of my customers installed PS 4 and also stopped having the issue, so you have a couple of options here.

    I did test the CTP versions and seem to be pretty good 🙂

    1. James says:

      Hi Jose,

      Excellent article. Instead of moving to the next MP or disabling the Counters. Is there someway to override the command and change it to invoke 2 ?
      powershell.exe -version 2 -NoLogo -NoProfile -Noninteractive “$ep = get-executionpolicy; if ($ep -gt ‘RemoteSigned’) {set-executionpolicy -Scope Process remotesigned} & ‘$$file/CPUUsagePercentDataSource.ps1$$’ -computerName ‘$Config/ComputerName$’ -SQL_WMI_NAMESPACE
      ‘ComputerManagement’ -serviceName ‘$Target/Property[Type=”SQL!Microsoft.SQLServer.DBEngine”]/ServiceName$’ -sqlInstanceName ‘$Target/Property[Type=”SQL!Microsoft.SQLServer.ServerRole”]/InstanceName$’ -connectionString ‘$Target/Property[Type=”SQL!Microsoft.SQLServer.DBEngine”]/ConnectionString$’
      -tcpPort ‘$Target/Property[Type=”SQL!Microsoft.SQLServer.DBEngine”]/TcpPort$’ -instanceId ‘$Target/Property[Type=”SQL!Microsoft.SQLServer.DBEngine”]/InstanceID$’

      1. Hi James,

        No, unfortunately that parameter is not passible to an override.

        Best regards,
        José Miguel

  3. B says:

    Thanks, Josè. We're seeing issues where the monitoringhost process taking up to 100% of the CPU on Windows 2003 server w/SQL 2005. Are there any reports on this as well? Thanks again.

  4. Hi B, if you’re using SQL MP 6.6.4.0 or 6.6.2.0, then the chances are high that you’re facing the same issue, since it contained the same change on the "CPUUsagePercentDataSource.ps1". But we haven’t had any reports with this SQL and OS versions.

  5. Paul_Hugill says:

    Hi José,
    Looks like my comment and your reply from yesterday have disappeared.

    We are mostly on SQL 2012 but there are a couple of SQL 2008R2 servers that had the issue too.

    If the CTP version has been stable like you say, then I will consider importing it if we keep seeing issues or we don’t hear a release date soon.
    It has only been a couple of occurrences in the last month so probably a little better than it had been but I think pushing out PS 4 might be a bit too much of a stretch for us, hopefully we can hold out until officially released.

    Thanks
    Paul

    1. Hi Paul,

      The SQL 2016 release date was announced last week and it’s going RTM on June 1st:
      https://blogs.technet.microsoft.com/dataplatforminsider/2016/05/02/get-ready-sql-server-2016-coming-on-june-1st/

      So we can expect the MPs to be release after that date, but I still don’t have a more concrete date; I’ll update this post once I hear back from the SQL engineering team.

      Best regards,
      José Miguel

  6. Laenaren says:

    Hey, We are experiencing the same issue using version 6.5.1.0, even after upgrading the powershell to version 4.0.
    What can be a possible solution?

    1. Hi Laenaren,

      Until now, I didn’t see any reports of this issue with SQL MP version 6.5.1.0.

      Probably you can try the CTP version or wait for the official release, but you can also raise a support case with us, to see if it is the same root cause.

      Best regards,
      José Miguel

  7. Stephen F Barbarino says:

    Any idea on when this will get released for general consumption?

  8. Theodor says:

    Dear José,
    Do you have an estimate when we can expect the techical preview to be converted/upgraded to an official release?

    Kind regards,
    Theodor

Skip to main content