Submit a ticketCall us

whitepaperYour VM Perplexities Called, and They Need You to Read This.

Virtualization can give you enormous flexibility with future workloads and can be a key enabler for other areas, like cloud computing and disaster recovery. So, how can you get a handle on the performance challenges in your virtual environment and manage deployments without erasing the potential upside? Learn the four key areas you need to be focusing on to help deliver a healthy and well-performing data center.

Get your free white paper.

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

CPU utilization is not displayed properly in DPA

Updated January 10, 2018


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


  • All versions of DPA


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.


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.
  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 @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]
            WHEN hyperthread_ratio = cpu_count
                THEN cpu_count
            ELSE (([cpu_count] - [hyperthread_ratio]) / ([cpu_count] /[hyperthread_ratio]))
            END AS [number_of_cores_per_cpu]
            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]
SELECT TOP 1 record_id
       ,dateadd(ms, - 1 * (@ms_now - [TIMESTAMP]), GetDate()) AS EventTime
       ,100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
       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
       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 (CASE WHEN OtherProcessUtilization < 0 THEN SQLProcessUtilization/a.number_of_physical_cpus
       ELSE SQLProcessUtilization END) AS SQLProcessUtilization
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:


  2. Open the default-metrics-queries.xml file in a text editor.
  3. Locate the metricsQuery with the following name property:
    <name>Instance CPU Utilization</name>
  4. Replace the default query with the query shown above.
  5. Save and close the file.
  6. Restart DPA.
Last modified