Submit a ticketCall us

Solarwinds & Cisco Live! Barcelona
Join us from the 29th of January to the 2nd of February at Cisco Live 2018 in Barcelona, where we will continue to show how monitoring the network with SolarWinds will keep you ahead of the game. At our booth (WEP 1A), we will demonstrate how SolarWinds network solutions can help. As a bonus, we are also hosting a pre-event webinar - Blame the Network, Hybrid IT Edition with our SolarWinds Head Geek™, Patrick Hubbard on January 24th - GMT (UTC+0): 10:00 a.m. to 11:00 a.m. There's still time to RSVP.

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

CPU utilization is not displayed properly in DPA

Updated January 10, 2018

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 (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:

    <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. 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

Tags

Classifications

Public