Submit a ticketCall us

Webinar: Web Help Desk for HR, Facilities and Accounting Departments
This webinar will focus on use cases for HR, Facilities and Accounting.

Having a unified ticketing and asset management system for all the departments in your company can provide end-users with a seamless experience and make things easier for your IT team. Yet, with different business tasks and objectives, many departments don’t fully understand the capabilities of Web Help Desk and how the software can be customized for effective use in their departments.
Register 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 (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. 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
09:14, 7 Sep 2017

Tags

Classifications

Public