Why is the processor % different in Activity Monitor vs Resource Monitor?

Question

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?

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

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.

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

Answer 1

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;

Answer 2

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.