3

In your opinion what is the best configuration for optimal SQL Server performance in regards to virtual guest Socket/CPU configuration? I've read numerous articles stating wide socket configuration (1 cpu per socket) provides for optimal performance. In other articles optimal performance for SQL Server is achieved with a 1 socket and 'x' number of cpus in that one socket. Matching the guest VM Socket/CPU to the physical host configuration.

SQL Server Standard is limited with 4 sockets, but what configuration have you found to perform best? We do plan to perform load testing in the near future. I look forward to your feedback. Thanks in advance!!!

1
  • 1
    You need to define your workload better to get a better answer. I've found that very few SQL workloads are cpu bound, memory and disk are the bottlenecks. Dec 8 '20 at 20:59
4

For most workloads, once a processor, the number of vcpus, and the amount of vRAM has been chosen for the vm, the best choice will vcpus and vRAM spread evenly across the smallest number of vNUMA nodes which allow the vcpus and vRAM to align with physical resources on the same number of physical NUMA nodes underneath.

This blog post from Mark Achtemichuk explains this idea quite well. Virtual Machine vCPU and vNUMA Rightsizing – Guidelines https://blogs.vmware.com/performance/2017/03/virtual-machine-vcpu-and-vnuma-rightsizing-rules-of-thumb.html

So, for SQL Server Standard Edition which allows the lesser of 4 virtual sockets or 24 vcpus: all other things being equal, all vcpus in a single vNUMA node, with all vcpus and vRAM coming from a single underlying physical NUMA node is prefered. If the amount of RAM(or the vcpus) for that vm won't fit in one physical NUMA node, split it across 2 NUMA nodes. Only go to 4 NUMA nodes if you have to. And... based on the analytics workloads I work with... try to stay on 4 NUMA nodes as long as possible before going to 8.

The blog post from from Mark Achtemichuk also links to some material from Frank Denneman - he delves into some of the specific VMware parameters that may be needed to balance a VM as desired.

While VMware has in the recent past recommended a default setting cores-per-scoket=1 for all Windows VMs including SQL Server up to 64 vcpus* to let ESXi automatically plan the vNUMA configuration, they are transitioning to recommending setting cores-per-socket to the expected number of vcpus in a vNUMA node.

I have seen one particular SQL Server workload and system where even though all vcpus and vRAM could fit on one vNUMA node on a single physical NUMA node, it was intentionally placed on 2 vNUMA nodes balanced across 2 physical NUMA nodes. This was done because a partiticular memory object was only partitioned at the node level and could not be dynamically partitioned to the cpu level as many objects can be. The result, while all vcpus were in a single vNUMA node, was significant spinlock contention that was affecting performance. This spinlock contention was relieved by splitting the same number of vcpus into two vNUMA nodes (and balancing those vNUMA nodes across 2 physical NUMA nodes). That is a very exceptional case, however, and quite frankly I expect them to transition back to a single vNUMA node on a single physical NUMA node as that workload matures.

*For Windows VMware VMs above 64 vcpus, cores-per-socket muct be set higher than one in order to conform to the Windows 64 socket limit.

0

You might want to read through some of the standard VMware literature available on the web.

Not the answer you're looking for? Browse other questions tagged or ask your own question.