4

I'm tracking a significant difference between the processor % reported in the Activity Monitor for SQL Server vs the total processor % used in the Resource Monitor. I'm seeing about a 50% difference between the two with the Activity Monitor always being higher.

System Specs:

  • SQL Server EE 2008 R2
  • Windows 2008 R2
  • Bare metal
  • 96 cores w/no processor affinity set (4 Physical Sockets with 12 cores each w/HyperThreading enabled)
  • 768 GB of memory
  • Active/Passive cluster
  • Direct attach storage of SSDs

What would cause such a difference?

Which one should I be looking at?

CPU Comparision

Update:

I see that resource monitor has it split between different categories however wouldn't the "CPU - Total" graph show all CPU usage on the machine?

The Task Manager Performance tab is showing the same difference between it and Activity Monitor.

Activity Monitor Vs Task Manager Performance

  • Activity Monitor is on a 1 sec refresh.

Could there be an issue with Activity Monitor not seeing that each core is HyperThreaded? Not sure how as SQL Server sees all 96 cores.

SQL Server Properties

Update 6/28/2018

Even after migrating to AWS and updating the OS to Server 2012 R2 the SQL Server 2008 r2 Activity Monitor is still dramatically off from what Task Manager shows. The below screen shot is from a load test that was just performed. There is a visible 20% difference between the two metrics. Even when Task Manager is reporting the overall CPU just over 55% the SQL Activity Monitor is pegged at 100%. Metrics from the AWS Cloud watch show the same results as Task Manager so I have two data metrics that correlate with a third that isn't even in the ballpark.

AWS Server

Updated System Specs: System Specs:

  • SQL Server EE 2008 R2
  • Windows 2012 Datacenter R2
  • i3.16xl - Dedicated Host
  • 64 cores w/no processor affinity set (2 Sockets with 32 cores each)
  • 488 GB of memory
  • Synchronous Mirroring
  • ENA Enabled w/EBS Optimization enabled

enter image description here

  • I ignore the Resource Monitor for CPU usage. – Randolph West Sep 3 '16 at 2:01
  • (ignore deleted comment...missed bit about 96 cores..although maybe one is missing that each core has 2 threads?) – mpag Sep 7 '16 at 18:15
  • 3
    looking at the resource monitor shot, you have 37% usage of the CPU by services. So a total of 69% usage between that and processes. Which is the the ballpark (within 5% overall, or 8% or so of the values), allowing for differences in sampling intervals and intensities for the two applications. – mpag Sep 7 '16 at 18:24
  • Can you please share your CPU settings of SQL Server?? – Rajesh Ranjan Sep 8 '16 at 7:30
6
+50

Resource monitor shows CPU from Processes and Services on two different graphs. (In your example, 32% + 37%, which means a total 69% CPU).

As for SQL Activity Monitor, it shows CPU utilization for the machine on which SQL is installed. (In your example, 64%, which is not that far from Resource monitor.)

I use a query to get the CPU utilization on my server. I run a job on my server and raise an alarm if I get an average over 90% for the last 10 minutes.

Here's my query:

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info); 

-- Top 10 : for the last 10 minutes
SELECT TOP(10)  100 - SystemIdle as TotalCPU,
           DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time] 
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;
  • Very nice query, Danielle. Do you know the oldest version of SQL this will work on? – datagod Sep 8 '16 at 14:34
  • Thanks! The oldest server I have access to is SQL Server 2012. I've never tried it on older servers. – Danielle Paquette-Harvey Sep 8 '16 at 14:38
  • 1
    @datagod, it should work on 2008 but not on 2005. Did you mean 2005 instead? There's a slight change for 2005. See here: social.msdn.microsoft.com/Forums/sqlserver/en-US/…. – Tara Kizer Sep 8 '16 at 14:49
  • 2
    @DaniellePaquette-Harvey Process Explorer by MS/sysinternals is a beautiful little program (everything you need in one EXE). I used Iarsn's taskinfo until I found Process Explorer. – mpag Sep 8 '16 at 21:33
  • 1
    @DaniellePaquette-Harvey yeah I'm going off what SQL server is saying. I'm just trying to understand what the difference is and if there is room for more load on the server that would be good to know. I have a prior generation server with the same OS and SQL versions and they track one to one. What a mystery. – Aaron Sep 8 '16 at 22:54
0

a few more details added to Danielle's query

DECLARE @processorGHz float = (SELECT 1.0*cpu_ticks/ms_ticks/(1000000) FROM sys.dm_os_sys_info); -- divided by 1 million as that is 1 billion (GHz) divided by 1000 (ms/s)
DECLARE @ts_now bigint = (SELECT ms_ticks from sys.dm_os_sys_info); --cpu_ticks/(cpu_ticks/ms_ticks) reduces to just ms_ticks. 1/(1/x)=x
DECLARE @processors int = (SELECT cpu_count from sys.dm_os_sys_info);
DECLARE @qs datetime = GETDATE();
-- Top 60 : for the last hour
SELECT TOP(60)
    DATEADD(ms, -1 * (@ts_now - [timestamp]), @qs) AS [Snapshot Time],
    100 - SystemIdle as [Total CPU Burden (%)],
    COALESCE(CAST(
        NULLIF(CONVERT(decimal(18, 4), 100.0 * UMT / (@processorGHz * 1000000 * 60 * @processors)),0.0)
    as varchar(10)),'') as [SQL Server Userspace CPU Usage (%)],
    COALESCE(CAST(
        NULLIF(CONVERT(decimal(18, 4), 100.0 * KMT / (@processorGHz * 1000000 * 60 * @processors)),0.0)
    as varchar(10)),'') as [SQL Server Kernel CPU Usage (%)],
    COALESCE(CAST(
        NULLIF(
            CONVERT(decimal(18, 4), 
                100.0 * UMT / (@processorGHz * 1000000 * 60 * @processors) +
                100.0 * KMT / (@processorGHz * 1000000 * 60 * @processors)),0.0)
    as varchar(10)),'') as [Total CPU Usage by SQL Server (%)],
    SPU as [SQL Processor Usage (% trunc)],
    Mem as [Total System Physical Memory Used (%)]
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 [SPU],
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/UserModeTime)[1]','int') AS UMT,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/KernelModeTime)[1]','int') AS KMT,
        record.value('(./Record/SchedulerMonitorEvent/SystemHealth/MemoryUtilization)[1]','int') AS Mem,
        [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;

I'd appreciate any feedback on if the SQL Server Userspace CPU Usage %-type columns are accurate. I use very little of my CPU resources for long enough to capture in the dataset on my server. Unfortunately the documentation for UserModeTime and KernelModeTime of sys.dm_os_ring_buffers is completely lacking at MSDN, so I'm not sure if it's measuring clock time, processor time, processor cycles or what. These numbers don't seem to be measuring all processes on the computer, as I'll often get 0s, but the processors are definitely being used, so I surmise they reflect CPU usage by SQL server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.