If you don’t license all of your SQL Server’s CPU cores, you need to pay particular attention to your server hardware.
Say you’re using an HP DL580 with four CPU sockets, each of which has a Xeon processor plugged in with 10 cores. That’s a total of 40 real cores, or 80 logical processors if you’ve enabled hyperthreading.
In SQL Server’s log at startup, if you’re using an upgraded core-based license capped out at 20 cores, you’ll see a message like this:
SQL Server detected 4 sockets with 10 cores per socket and 20 logical processors per socket, 80 total logical processors; using 40 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
To understand the performance implications, it helps to look at a diagram of the server’s system board:
The 4-3-2-1 across the top are the four Xeon processors, each of which has 10 cores (20 with hyperthreading.)
The 8-7-6-5-4-3-2-1 across the bottom are the memory banks. Memory is not shared across all CPUs – each CPU has its own local banks of memory, a critical concept in NUMA – non-uniform memory access. To learn more about NUMA, check out the virtualization module in my Senior DBA Class.
Here’s what that concept means in practice for our server – say we’ve got 1TB of total memory:
Each processor gets its own local memory.
And when SQL Server says, “Hey, you’re only licensed for half of these cores, so I’m only going to turn on half of them,” it will only use the memory in half of the NUMA nodes. In this case, we’re licensed for 40 logical processors, so we have to dig further into DMVs and logs to figure out which NUMA nodes got turned on, and whether or not our memory is even available to us. See, if memory is hooked up to a NUMA node that isn’t enabled, that memory won’t be accessible to SQL Server.
Similar problems exist when you run Standard Edition on a server with more than 4 CPU sockets. Sounds crazy – most of us don’t use servers that large – but this situation pops up a lot when VMs are misconfigured with 8 virtual CPUs, each of which is presented to the guest as its own CPU socket.
How to Know If Your SQL Server is Affected
Run sp_Blitz® and look for the priority 50 warning of “CPU Schedulers Offline.”
How to Fix It
There are lots of ways to fix it, and the right solution depends on your version/edition of SQL Server, your licensing, and your hardware. Here’s the big picture:
Make sure you installed the right binaries. Believe it or not, there’s actually an install for SQL Server Enterprise Edition floating around that’s capped at just 20 cores. Even though you might have paid for more, if you run the wrong installer, sad trombone. Here’s more info about the infamous 20-core-limited installer.
One option is to license all of your sockets and cores. (Hey, listen, you’re the one who wanted a solution.) Don’t buy hardware with more cores than you actually need because CPUs with more cores typically have slower clock speeds (often by 1/3 or 1/2) compared to CPUs with less cores. If you’re limited by SQL 2014 Standard Edition & prior’s cap at 16 cores, you could consider upgrading to SQL 2016 Standard Edition, which will allow you to license up to 24 cores. More than that, though, and you’re looking at Enterprise Edition.
Another option is to use affinity masking to tell SQL Server, “These are the specific cores I want you to use,” and manually load balance the work across all NUMA nodes. Say you’re using SQL Server 2014 Standard Edition, which is limited to 4 sockets or 16 cores, whichever comes first. In the example above, where we have 4 sockets with 10 cores each, you could design your affinity mask to just use 4 cores per socket (4 x 4 = 16) thereby staying within the 16-core limit.
If it’s a VM, tweak the cores/sockets configuration. Some folks accidentally configure an 8-socket, 1-core-per-socket VM, which has 8 cores – but only 4 of them are usable. Just shut your VM down, change your VM cores-per-socket settings, and boot ‘er on back up.
For custom advice on which one is right for you, check out our consulting services.