Submit a ticketCall us

Get a crash course on Network Monitoring delivered right to your inbox
This free 7-day email course provides a primer to the philosophy, theory, and fundamental concepts involved in IT monitoring. Lessons will explain not only how to perform various monitoring tasks, but why and when you should use them. Sign up now.

Home > Success Center > Database Performance Analyzer (DPA) > CPU utilization is not displayed properly in DPA

CPU utilization is not displayed properly in DPA

Updated February 14, 2017

Overview

In certain environments with high CPU usage, DPA may display incorrect metrics in the Instance CPU Utilization resource chart.

Environment

  • All versions of DPA

Cause

DPA uses an unsupported view that receives inconsistent results from a SQL Server instance running on multiple physical/logical socket/cores configurations. The issue is with Microsoft SQL Server collecting the wrong value. The Instance CPU Utilization resource chart receives data from an unsuported Dynamic Management View (DMV) (sys.dm_os_ring_buffers).

For more information, see this article from Microsoft.

Resolution

Select one of the two workarounds:

Enable WMI metrics in DPA

  1. Click Options > Administration tab > Advanced Options.
  2. Click the DB Instance Options tab, and select the SQL Server database instance.
  3. Select the Support Options check box.
  4. Click the SQL_SERVER_WMI_METRICS_ENABLED option.
  5. Set the New Value to True, and click Update.

WMI metrics may negatively affect your monitored instance. See this article for more information.

See this article for more information on WMI metrics.

Use an alternative query

Test this query outside of DPA first:

DECLARE @ms_now BIGINT
DECLARE @xp_msver TABLE (
    [idx] [int] NULL
    ,[c_name] [varchar](100) NULL
    ,[int_val] [float] NULL
    ,[c_val] [varchar](128) NULL
    )
 
SELECT @ms_now = ms_ticks
FROM sys.dm_os_sys_info;
 
INSERT INTO @xp_msver
EXEC ('[master]..[xp_msver]');;
 
WITH [ProcessorInfo_cte]
AS (
    SELECT ([cpu_count] / [hyperthread_ratio]) AS[number_of_physical_cpus]
        ,CASE
            WHEN hyperthread_ratio = cpu_count
                THEN cpu_count
            ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] /[hyperthread_ratio]))
            END AS [number_of_cores_per_cpu]
        ,CASE
            WHEN hyperthread_ratio = cpu_count
                THEN cpu_count
            ELSE ([cpu_count] / [hyperthread_ratio]) * (([cpu_count] -[hyperthread_ratio]) / ([cpu_count] / [hyperthread_ratio]))
            END AS [total_number_of_cores]
        ,[cpu_count] AS [number_of_virtual_cpus]
        ,(
            SELECT [c_val]
            FROM @xp_msver
            WHERE [c_name] = 'Platform'
            ) AS [cpu_category]
    FROM [sys].[dm_os_sys_info]
    )
,utilization_cte
AS(
SELECT TOP 1 record_id
       ,dateadd(ms, - 1 * (@ms_now - [timestamp]), GetDate()) AS EventTime
       ,SQLProcessUtilization
       ,SystemIdle
       ,100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
FROM (
       SELECT record.value('(./Record/@id)[1]', 'int') AS record_id
             ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle
             ,record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization
              ,TIMESTAMP
       FROM (
              SELECT TIMESTAMP
                     ,convert(XML, record) AS record
              FROM sys.dm_os_ring_buffers
              WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
                     AND record LIKE '%<SystemHealth>%'
              ) AS x
       ) AS y
       ORDER BY record_id desc
)
 
SELECT EventTime
,(CASE WHEN OtherProcessUtilization < 0 THEN SQLProcessUtilization/a.number_of_physical_cpus
       ELSE SQLProcessUtilization END) AS SQLProcessUtilization
,SystemIdle
from utilization_cte
CROSS APPLY (SELECT (number_of_physical_cpus*1.0) AS[number_of_physical_cpus] FROM [ProcessorInfo_cte]) AS a

If it solves the issue, you can continue and replace it in a DPA configuration file.

Upgrading DPA restores this configuration file to its original state.

  1. Go to the following directory:

    <DPA_install_dir>/iwc/tomcat/webapps/iwc/WEB-INF/classes/resources/metrics/

  2. Open the default-metrics-queries.xml file in a text editor.
  3. Go to line 2777, and replace the default query with the query shown above.
  4. Save and close the file.
  5. Restart DPA.
Last modified
08:36, 14 Feb 2017

Tags

Classifications

Public