3 New information added from migration to AWS
source | link

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

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

    Notice removed Authoritative reference needed by Community
    Bounty Ended with Danielle Paquette-Harvey's answer chosen by Community
    Tweeted twitter.com/StackDBAs/status/774081239636512768
2 Added more details.
source | link

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

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

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

    Notice added Authoritative reference needed by Aaron
    Bounty Started worth 50 reputation by Aaron
1
source | link

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

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