Sometimes, a seemingly insignificant configuration choice can have a profound impact on the performance of your SQL Server. In this short post, I will discuss a problematic CPU configuration I have come across at several companies. It often goes unnoticed until the server is under load and doesn’t perform at the level that would be expected of the hardware.
The Scenario
Let’s say you’re building a new SQL Server instance on VMware. Your company doesn’t have the budget for Enterprise Edition, but still wants High Availability (HA). The decision is made to go with a 2-node AlwaysOn Failover Cluster Instance (FCI) running SQL Server 2019 Standard Edition – a common topology for a highly-available SQL Server.
Even though Enterprise was outside the budget, management wants to make sure they’re getting everything they can out of Standard Edition. You recall from the Feature Comparison that Standard can use up to 24 cores and 128GB of RAM. Off go the server requests to your VMware administrator and soon you have two new servers ready for SQL Server.
Installation goes without a hitch and everything looks good. You then check the SQL Server error log for any abnormalities after installation because you’re a responsible DBA. Much to your surprise, you see this message:
SQL Server detected 12 sockets with 2 cores per socket and 2 logical processors per socket, 24 total logical processors; using 8 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
Uh oh, what’s happening? Only a third of your 24 logical processors are being used by SQL Server! Microsoft says no user action is required, but is that correct?
In short, no.
Six of One, Half a Dozen of the Other
What’s happening here is you ran into the CPU socket limitation of Standard Edition. Your memory did serve you correctly that Standard can use 24 cores. The actual limitation, however, is defined as “Limited to lesser of 4 sockets or 24 cores”. The 24 cores are simply spread across too many sockets. Your helpful VMware admin built your servers to what would otherwise be a totally acceptable spec, but not for SQL Server Standard Edition. Fortunately, it’s typically pretty easy to fix by simply reducing the total sockets and increasing the number of cores per socket.
There is a Catch
It’s going to require some additional discussion with your VMware admin to ensure your VMs are configured for optimal performance. The reason is beyond the scope of this post, but has to do with performance of NUMA/vNUMA. You can read more about the details and recommended configurations straight from VMware at this link.
In Summary
You should always make sure that SQL Server is using all the CPU resources that are presented. Nobody likes a crippled database server. You also don’t want to have to explain to your boss that they’ve been paying for SQL Server core licenses that the database engine couldn’t even use.
This is just one of the many configuration checks included in a comprehensive server Health Check by SqlCS. Please get in touch if you want the piece of mind of knowing that your SQL Server instances are operating at their full potential.