通过

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 
 
 

Comments

  • Anonymous
    April 11, 2016
    The comment has been removed
  • Anonymous
    April 11, 2016
    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 :)
    • Anonymous
      May 04, 2016
      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$’
      • Anonymous
        May 08, 2016
        Hi James,No, unfortunately that parameter is not passible to an override.Best regards,José Miguel
  • Anonymous
    April 12, 2016
    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.
  • Anonymous
    April 12, 2016
    The comment has been removed
  • Anonymous
    April 28, 2016
    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.ThanksPaul
    • Anonymous
      May 08, 2016
      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
  • Anonymous
    May 01, 2016
    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?
    • Anonymous
      May 08, 2016
      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
  • Anonymous
    May 31, 2016
    Any idea on when this will get released for general consumption?
  • Anonymous
    June 15, 2016
    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