Background
2 PHYSICAL cluster nodes - each 4 sockets x 20 cores (80 logical processors), 1TB RAM
4 x Intel Xeon E7-8891 , 10 core(s), 20 Logical processor
Each one has 8~9 virtual SQL Server 2017 STANDARD instances, with current "max degree of parallelism" MOP settings from 2 to 4
Version Info
Microsoft SQL Server 2017 (RTM-CU6) (KB4101464) - 14.0.3025.34 (X64)
Apr 9 2018 18:00:41 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 (Build 9600: )
Now, I see warnings from sp_Blitz
CPU Schedulers Offline NULL https://BrentOzar.com/go/schedulers Some CPU cores are not accessible to SQL Server due to affinity masking or licensing problems.
Memory Nodes Offline NULL https://BrentOzar.com/go/schedulers Due to affinity masking or licensing problems, some of the memory may not be available.
Here’s the catch: the lesser of 4 sockets or 24/16 cores
When I run below query (see results)
SELECT * FROM sys.dm_os_schedulers --115 total
where status LIKE '%hidden%' --32 VISIBLE OFFLINE; 34 HIDDEN ONLINE
And Task Manager CPU graph is 20~30% overall on each node (maybe 24 are heavily used, rest not used, at 0-1% only, see image)
I tried to search Error log for "Licensing" but cannot find such message indicating only some cores are used
My questions
Does "lesser of 4 sockets or 24 cores" mean my SQL instance (EACH) will only use 24 cores (despite 80 available)?
What should my MOP setting be, per each Instance? Are they smart enough to "share" the available CPU/NUMA? e.g. if there are 80 processors and 8 instances, they will not try to always use the first 24 processors
Current ones range from 2-4, but I think it should be at least 4
When I ran few Microsoft calculators, they all say 8
FYI: All instances are set to "Auto" set affinitiy/IO