A wonderful gentleman who attended my SQL PASS Summit session this year recently emailed me and asked me a great question about optimal setup for SQL Server virtual machine vCPU and vNUMA configurations. I thought about the question and realized that while the session went into a light level of detail, I can go much deeper here on my blog.
While the examples and screenshots in this post are all from my VMware vSphere lab environment, the same concepts directly apply to all other hypervisors on the market.
First, what is NUMA?
NUMA, or non-uniform memory access, is an architectural approach in computer hardware design that places banks of memory in close proximity to each physical CPU socket. It adds efficiency to the system as CPU counts and memory capacity increase, which in turn improves performance. The proximity of the memory bank is extended into the operating system so that applications can be coded for workload efficiency. SQL Server is NUMA aware, and therefore takes advantage of this awareness. More information on NUMA can be found over at Wikipedia.
The trick with NUMA and virtual machines is to determine how to construct a virtual machine to take advantage of NUMA when performance truly counts. Some virtual machines are somewhat idle and not resource intensive at all, and these are generally less impactful with the performance tuning. However, other virtual machines are absolute resource hogs and consume quite a bit of resources to satisfy their workload requirements. These workloads will certainly benefit from NUMA.
With VMware vSphere, the ability to extend these NUMA details into a virtual machine was first brought to us in vSphere 5.0. Prior to this point, you could just select the number of vCPUs that a VM was assigned, and the hypervisor was left to balance the CPU and memory requests.
Host Physical Machine
The modern physical servers in a virtualization server farm all have individual NUMA configurations if they have more than one CPU socket. From my home lab, the vCenter management interface tells me the CPU configuration of the physical server.
Continue reading on DavidKlee.net.