Understanding SQL Monitor "CPU Utilization (%)" and Rule "MSSQL on Windows: DB Engine CPU Utilization (%)"

ANU 336 Reputation points
2022-11-25T13:27:28.473+00:00

Hello All,

Thanks in advance:

From the monitor "CPU Utilization (%)", we are receiving alerts "MSSQL on Windows: CPU Utilization (%) is too high". I would like to know how the monitor is computing the CPU utilization value. Not mentioned much information in the product knowledge.

Monitor "CPU Utilization (%)" and Rule "MSSQL on Windows: DB Engine CPU Utilization (%)" have similar configuration. Whether the Same sampled data is used for the Alert generation and performance collection?

Does the collected performance data is also used for Alert generating. While checking the performance view the collected data value is showing more than 100% for some time. Why it is going above 100%?
During the same period the CPU of the machine hosting the SQL instance was around 60%.
264284-sqlmonitor.jpg264301-sqlrule.jpg
Regards,
ANU

Operations Manager
Operations Manager
A family of System Center products that provide infrastructure monitoring, help ensure the predictable performance and availability of vital applications, and offer comprehensive monitoring for datacenters and cloud, both private and public.
1,443 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. SChalakov 10,371 Reputation points MVP
    2022-11-30T08:23:49.74+00:00

    Hi Anu,

    about that counter, here is the information you wanted:

    This is the data source Module Type:

    MSSQL on Windows: CPU Utilization Mapped Provider
    https://systemcenter.wiki/?GetElement=Microsoft.SQLServer.Windows.DataSource.CPUUtilizationMapped&Type=DataSourceModuleType&ManagementPack=Microsoft.SQLServer.Windows.Monitoring&Version=7.0.0.0

    for the performance colletction rule:

    Microsoft.SQLServer.Windows.CollectionRule.DBEngine.CPUUtilization (Rule)
    https://systemcenter.wiki/?GetElement=Microsoft.SQLServer.Windows.CollectionRule.DBEngine.CPUUtilization&Type=Rule&ManagementPack=Microsoft.SQLServer.Windows.Monitoring&Version=7.0.0.0

    The actual counter is:

            <ConditionDetection ID="Mapper" TypeID="SystemPerf!System.Performance.DataGenericMapper">  
              <ObjectName>SQL DB Engine</ObjectName>  
              <CounterName>CPU Utilization (%)</CounterName>  
              <InstanceName/>  
              <Value>$Data/Property[@Name='CPUUtilization']$</Value>  
            </ConditionDetection>  
    

    About the monitor:

    Microsoft.SQLServer.Windows.MonitorType.DBEngine.CPUUtilization (UnitMonitorType)
    https://systemcenter.wiki/?GetElement=Microsoft.SQLServer.Windows.MonitorType.DBEngine.CPUUtilization&Type=UnitMonitorType&ManagementPack=Microsoft.SQLServer.Windows.Monitoring&Version=7.0.0.0

    265652-image.png

    As you see from the screenshot, it uses the same data source, same counters.
    The one is a performance collection rule, which gathers the counter, the other is a monitor, based on the same counter.

    About the value being over 100%, I think this coould also play a role (from the rule link):

    Percentage of CPU utilization depends on the processors affinity mask is set for the SQL Server instance. If the affinity mask is set automatically for all processors, the accumulated value will be counted with consideration for all processors’ cores according to the SQL Server edition.

    Hope this helps.

    ----------

    (If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)
    Regards
    Stoyan Chalakov

    1 person found this answer helpful.
    0 comments No comments