(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)
Today I want to talk about a very interesting topic in VMware vSphere: the Cores per Socket option, and how you should configure it for your SQL Server based Virtual Machine.
The VMware Recommendation
The recommendation from VMware is that you set the Cores per Socket option to 1. This means that you are getting for each vCPU that you have assigned to your Virtual Machine 1 virtual Socket (vSocket) within your Virtual Machine. It is recommended by VMware, because with this setting, ESXi also represents the correct NUMA topology to your Virtual Machine.
As you know from my Home Lab deployment, I have in each physical ESXi Host 2 physical CPU Sockets, each with 8 physical Cores. If I create a Virtual Machine with 16 vCPUs, and I set Cores per Socket to 1, I get within that Virtual Machine 16 vSockets and 2 NUMA Nodes.
Therefore SQL Server can perform its NUMA optimizations and everything is fine. Is everything fine?
A good recommendation?
No, nothing is fine with that recommendation, because the Virtual Machine has now 16 vSockets. And that is a huge problem for SQL Server – in the Standard Edition. The Standard Edition has some limitations on how many CPU resources you can use:
- 24 CPU Cores
- 4 CPU Sockets
As you can see, the Standard Edition is restricted to only 4 CPU Sockets. In my case I have a Virtual Machine with 16 vSockets – much more. And how many CPU Cores is SQL Server using? Only 4, because we have in each Socket only 1 Core available:
In that case, SQL Server has now only 4 Schedulers in the state VISIBLE_ONLINE, and all the others are just VISIBLE_OFFLINE. But you can work around that problem by reconfiguring the Cores per Socket option of your Virtual Machine. Let’s give our Virtual Machine 8 Cores per Socket. This gives us now only 2 vSockets in the Virtual Machine. Let’s check the SQL Server Log again:
As you can see now, SQL Server is now able to use all 16 vCPUs, because they are only spread across 2 vSockets. And SQL Server still sees the correct NUMA topology consisting of 2 NUMA Nodes.
Caution: Please also check out this blog posting from VMware, where they describe the vNUMA changes implemented in ESXi 6.5+. In earlier versions of ESXi (<= 6.0), you could also have a wrong vNUMA topology, if you set a custom Cores per Socket value!!!
Summary
Recommendations are just recommendations. Nothing more. VMware made the recommendation to set the Cores per Socket to 1, so that the correct vNUMA topology can be presented to your Virtual Machine. But in combination with SQL Server Standard Edition, you can get quite fast into serious troubles, because SQL Server is not able to use all vCPUs!
If you are more interested in how to Design, Deploy, and Optimize SQL Server on VMware vSphere, you can also check-out my Online Training about it.
Thanks for your time,
-Klaus
