When you install SQL Server Standard or Web Edition in a virtual machine with more than 4 cores, things can look like they’re just fine – when in reality, you’re not using anywhere near your server’s potential. See, these editions have a tricky limitation – let’s look at the documentation:
Here’s the catch: the lesser of 4 sockets or 24/16 cores. Not the greater – the lesser. So if you configure a virtual machine with, say, 8 virtual processors, you have to be really sure that you don’t accidentally end up with 8 sockets. Here’s a screenshot of Task Manager in a poorly configured VM:
At the right, see how it says “Sockets: 8”? That means we set the VM up with 8 CPUs, each of which has a single core. Standard Edition will only use the first 4 cores here, and that’s it. No matter how much load our SQL Server gets, it’ll only use 4 cores – meaning CPU will only hit 50% busy. Our wait stats will show SOS_SCHEDULER_YIELD out the wazoo, but our sysadmins will say, “There’s plenty of available CPU power – you’re just not using it.”
Here’s an example of a reader who ran into the problem after reading this post – note how their first four cores are getting hammered, and the rest are sitting idle:
— Jose Miguel Requena (@reckenna) June 14, 2018
This is notoriously tricky because if you open SQL Server Management Studio, right-click on the server, and click Properties, it looks like everything’s okay. You see all 8 cores – you just can’t use ’em because they’re offline, as shown in sys.dm_os_schedulers:
There’s another clue buried in the SQL Server startup log:
SQL Server detected 8 sockets with 1 cores per socket and 1 logical processors per socket, 8 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
Just informational. No big deal. Carry on.
How to tell if you’re having this problem – and fix it
Just run sp_Blitz, and look for the warning of “CPU Cores Offline” or “Memory Nodes Offline.” This is one of those things I found once in the field, figured I’d better check for it, and now I find it all over the place. I totally understand why – from the SQL Server side, nothing looks out of the ordinary. (To date, SQL ConstantCare® has found this problem on 27 servers!)
The solution is easy:
- Shut down the VM
- Change the number of cores per socket (VMware instructions) – in my case, I could set it to a single-socket, 8-cores-per-socket VM, or a 2-socket quad-core, or a 4-socket dual-core
- Turn the VM back on
And enjoy your newfound power. Be aware, of course, that your CPU percent-busy may suddenly get higher (worse) – but that’s a good thing, because it means you’re using all that horsepower you paid for.
Bonus track: demoing this in VMware Fusion
VMware doesn’t enable you to build really dumb VM configurations by default. You have to go into your VM library, hold down the Option key on your keyboard, right-click on the VM, and continue holding down the Option key as you click “Open Config File in Editor.” The server-grade vSphere documentation applies to Fusion, too, so here’s how I built a 12-core guest with offline CPU cores and memory nodes:
numvcpus = "12"
cpuid.coresPerSocket = "1"
numa.vcpu.maxPerVirtualNode = "2"
numa.vcpu.min = "4"
Needless to say, that’s not a good idea for anything but a test lab.