In this post I cover a fairly common VM CPU configuration mistake that may end up hurting your SQL Server’s performance and wasting licensing money.
Intro
It’s not uncommon that I run into a VM that’s configured with something like 6 or more cores with each core on one socket.
Here’s an example how this would show up in Task Manager for a VM with 16 CPU cores configured with 1 core per socket.
This is either a result of server/Windows admins being a bit to hasty when provisioning the VMs or due to them not being familiar with how this ends up impacting SQL Server.
How does this VM CPU misconfiguration hurt SQL Server?
Reading through the Compute Capacity Limits MS Learn page for SQL Server, you will notice this:
| SQL Server edition | Maximum compute capacity for a single instance (SQL Server Database Engine) | Maximum compute capacity for a single instance (AS, RS) |
|---|---|---|
| Enterprise edition: Core-based licensing 1 | Operating system maximum | Operating system maximum |
| Developer | Operating system maximum | Operating system maximum |
| Standard | Limited to lesser of 4 sockets or 24 cores | Limited to lesser of 4 sockets or 24 cores |
| Express | Limited to lesser of 1 socket or 4 cores | Limited to lesser of 1 socket or 4 cores |
The relevant portion here is the mention about SQL Server Standard Edition being limited to the lesser of 4 sockets or 24 cores.
Meaning that if your VM is configured with 24 CPU cores, but each core is on its own socket, then SQL Server will only “see” the first 4 sockets.
Thus limiting it to 4 cores.
At this point you already see how this might go wrong:
- A VM with 8 CPU cores is requested.
- The VM is provisioned with with a 1 core per socket CPU layout.
- The company forks out the $15,780 required for 4×2 core packs of SQL Server Standard licenses.
- SQL Server Standard Edition is installed.
- Performance issues may start popping up.
- Eventually someone ends up asking:
“Hey, why doesn’t SQL Server use all the cores on this VM?”
What happens in this example is that the Standard Edition instance, although being licensed for 8 CPU cores, will hit that 4 sockets limit first and not be able to access/use the rest of the cores.
As a result, the instance can only use half of the CPU cores on the VM and licensing money is just wasted on the unused cores.
How to spot this
Check for any offline schedulers
| 1 2 3 | SELECT * FROM sys.dm_os_schedulers WHERE is_online = 0; |
If the above query returns any records then you have a situation where SQL Server doesn’t have access to some of the cores on its host.
sp_Blitz
Brent Ozar’s sp_Blitz has a check (ID 101) specifically for this.
sp_PerfCheck
Erik Darling’s sp_PerfCheck also has a check (ID 4001) for this.
PSBlitz
Since PSBlitz uses a modified, non-stored procedure, version of sp_Blitz, it also checks for this CPU misconfiguration or any other issue resulting in offline schedulers.
The information can be found on the “Instance Health” report page.
SQL Server log
The following informational message is recorded in the SQL Server log as part of the startup sequence:
| Source | Message | Log Type |
|---|---|---|
| Server | 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. | SQL Server |
This isn’t really as helpful in catching your attention as it should be.
It’s marked as an informational message despite it being an obvious misconfiguration.
Task Manager
Check the Performance tab in Task Manager.
If you see more than 4 sockets on a SQL Server Standard Edition host, then the CPU cores on the rest of the sockets are not being used.
How to fix it
To fix this:
- Schedule some down time for your instance.
- Have your server admin shut down the VM.
- Change the VM’s CPU configuration from one core per socket to a single socket with multiple cores.
- Start the VM back up.
Once SQL Server starts it should be able to take advantage of all 8 CPU cores.
Conclusion
When running Standard Edition, it’s worth checking how your VM’s CPU configuration looks in order to avoid potential performance issues and wasting SQL Server licenses.