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