SQL Server 2012 – Troubleshooting CPU Schedulers Offline

sp-blitzsql serversql-server-2012

I logged onto a new clients systems and ran sp_blitz to see what's shaking. It reports back that "CPU Schedulers Offline" which is a new one for me.

Some CPU cores are not accessible to SQL Server due to affinity masking or licensing problems.

Fair enough, I run the base query

SELECT
    DOS.is_online
,   DOS.status
,   DOS.*
FROM
    sys.dm_os_schedulers AS DOS
ORDER BY
    1;

That reports back that I have 8 set to VISIBLE OFFLINE, 43 to online. To my knowledge, no one at this client would have intentionally set any CPU affinity.

I decided to see if I could unbugger it. When I look at the properties window, I see 40 processors available and none of them set to have affinity.

enter image description here

Why there's 40 showing yet 43 entries in the dm_os_schedulers where is_online is true seems curious as well. The cpu_id of the 8 offline are 32 to 39.

sys.configurations seems to concur with affinity not being explicitly on

name                 value  value_in_use  description
affinity I/O mask    0      0             affinity I/O mask
affinity mask        0      0             affinity mask
affinity64 I/O mask  0      0             affinity64 I/O mask
affinity64 mask      0      0             affinity64 mask

This isn't an Enterprise Edition so the CAL grandfather thing shouldn't be factor here but I can ask that question tomorrow if need be

ProductVersion  ProductLevel  ProductUpdateLevel  Edition
11.0.5058.0     SP2           NULL                Standard Edition (64-bit)

Running Glenn Berry's diagnostic queries, this stuff might be relevant

  • System Manufacturer: 'Dell Inc.', System Model: 'PowerEdge R720'.
  • Intel(R) Xeon(R) CPU E5-2680 v2 @ 2.80GHz

So, what gives? Am I virtualized and don't know it? Is there some other place I should be looking to determine why SQL Server can't use some of the CPUs?

Reference articles

A list of articles I read but clearly didn't comprehend well enough to answer my own question

Best Answer

What you see makes perfect sense given the information available.

SQL Server 2012 Standard Edition is limited to the lesser of 4 sockets or 16 physical cores.

Since you have 40 cores (likely 2 socket 10 core + HT) 16 physical cores is going to be 32 logical cores (because of HT). If you have 40 total, I would EXPECT 8 to be VISIBLE OFFLINE due to licensing.

Features Supported by the Editions of SQL Server 2012 - Cross-Box Scale Limits

Edit: Additional Information

Depending on the OS involved the schedules may be bound to CPUs that are also making an unbalanced NUMA memory configuration for your system. While it is highly unlikely that this will cause you any issues (since it wasn't noticed that all the CPUs were being used in the first place) it could have a much higher impact on a highly used system where the foreign memory access can really add up.

Related Question