Understanding the fundamentals of how CPU, memory, disk, and network interact in a virtual infrastructure will go a long way towards helping IT properly allocate virtual resources and make the most effective use of SQL licensing.
One of the driving forces behind SQL Server virtualization has been IT's ability to leverage the scaling and resiliency inherent with virtualized infrastructures while simultaneously reducing hardware costs. Additionally, a well-planned virtualization initiative can consolidate SQL servers and reduce the number of required SQL licenses.
The path of least resistance might seem to be configuring a VM that hosts SQL with as many processors as is allowed in SQL licensing - i.e. configuring SQL Server 2016 Standard edition with its maximum of 4 sockets/24 cores, and then deploying as many database instances as needed.
However: overcommitting a virtual SQL Server should be avoided. SQL Server is optimized for performance at the expense of resource utilization, and will use as much of its allocated resource capacity as possible. Allocating more resources than SQL needs will not appreciably improve SQL performance and will mean that resources are not available for other VMs.
Getting the best overall performance from both virtualization and SQL means keeping in mind SQL specific criteria for CPU, memory, disk and network configurations when creating VMs.
SQL Server is resource intensive and should not be forced to contend for resources with other VMs, so you’ll want to ensure that the CPU resources on the host are not overallocated. An increase in wait time for CPU due to overallocation can cause SQL latency issues that in turn can have a cascading effect on all the applications dependent on SQL Server.
In addition, do not take hyperthreading into account when calculating the number of available vCPUs (virtual CPUs). Hyperthreading adds a second processor thread per CPU that is available for use when the CPU would otherwise be idle. While this can increase overall processor performance by decreasing processor idle time, it is possible that two threads competing for a processor's resources will adversely affect SQL performance.
CPU Ready (VMware) or CPU Wait Time Per Dispatch (Hyper-V)
If you do overcommit CPU resources on a host, the CPU Ready or CPU Wait Time Per Dispatch metrics will measure the time that a VM has to wait for available CPU resources from the host.
The metrics show the level of CPU overcommitment, where the current demand for vCPUs exceeds the available supply and causes VMs to wait for vCPUs to become available.
For example, if you’re operating a host with a total capacity of 16 vCPU’s and they are all busy executing for the currently running VMs, while at the same time you have a virtual SQL Server configured with 8 vCPUs that is waiting for CPU resources, then the server must wait for 8 vCPU’s to free up before it has its turn to access the host CPU resources. The hypervisor won’t allow a multi-processor VM to operate until the number of available vCPUs match what is allocated to the VM.
|Longitude Report showing high CPU Ready %|
Non Uniform Memory Architecture (NUMA)
NUMA is a computer architecture designed to improve a processor's access to memory by grouping memory and processors into nodes. Memory that is in the same node as a processor is "local" and can be accessed with lower latency than the "remote" or "foreign" memory on another node.
The most straight-forward scenario for allocating resources for a SQL VM is if its resource requirements fit within the resources available in a NUMA node. For example: a host has 4 sockets/8 cores per socket (for 32 total cores) and 256 GB RAM, providing 4 NUMA nodes of 8 cores and 64 GB RAM each. In this configuration, you can create a VM with up to 8 cores and 64 GB RAM within one NUMA node.
If you need to create a SQL VM with more resources than exist in a single NUMA node, you can use virtual NUMA. Virtual NUMA is supported on SQL2005 and higher, and on Windows 2012 and higher, and will present an emulated NUMA architecture to the VM that reflects the underlying hardware. In our previous example of an 8 core/64 GB NUMA node, if the SQL VM required 8 cores and 96 GB RAM, then it would be configured across 2 NUMA nodes, with 4 vCPU per node, and 48 GB memory per node.
Guidelines for configuring VMs with virtual NUMA include assigning an even number of vCPUs to a VM and distributing them evenly across the physical NUMA nodes. However, there are some potential problems with VMs that span NUMA nodes:
- Enabling vCPU Host Add in VMware, or Dynamic Memory in Hyper V will disable virtual NUMA. VMs that span NUMA nodes without virtual NUMA will suffer performance degradation when their CPUs access memory on remote nodes.
- If a VM using virtual NUMA migrates to a new host and the underlying NUMA architecture on the new host differs, the VM's performance could suffer.
SQL Server Virtualization CPU Best Practice
SQL Server consumes lots of memory! The good news, when configuring and purchasing virtualization hardware - memory is one of the least expensive components. However, if you are under budgetary constraints when purchasing your virtualization hosts, you can get the most out of later upgrades by configuring your server with larger memory modules in fewer slots, providing open slots for additional memory modules later. Check the server specs carefully to determine compatible memory and how new modules need to be balanced across memory slots.
Virtualization allows for the overcommitment of memory in which the total amount of memory allocated for use by the VMs exceeds the physical memory of the host. This works as long as the memory actively used across all VMs does not approach the physical limit of the hosts. When that happens, memory reclamation techniques reclaim memory from the VMs, and this can cause severe performance degradation, especially for memory sensitive applications like SQL. Therefore - it is strongly recommended that you do not overcommit memory on hosts running SQL VMs.
To get the most use out of host memory without overcommitment it is important to determine how much memory is actively being used by all the VMs and to adjust the size of their memory allocations appropriately. This can be done by collecting memory usage metrics for existing VMs and using capacity planning to model memory on the host. Ideally you will want to try out different scenarios as part of your capacity planning exercise so that you can see happens when additional VMs with defined workloads are deployed on the host.
|Longitude Capacity Planner showing allocated memory exceeding capacity|
Memory Reservation (VMware)
In VMware, VMs are not given the memory they are allocated when they are powered on, but rather are assigned memory as it is used. Setting up a memory reservation for a VM sets aside the specified amount of memory for the exclusive use of the VM. This is often used to ensure that memory reclamation due to overcommitment doesn't take memory from a VM, but it can be used to ensure that a VM will be guaranteed a minimum amount of memory before it can be powered on.
SQL Server Virtualization Memory Best Practice
SQL Databases are inherently I/O bound, and using low latency, high throughput storage can lower the risk of I/O performance bottlenecks. The storage arrays used for VMs typically have the increased IO speed, capacity and reliability needed for standalone SQL servers.
While the underlying arrays are abstracted into virtual disks for the VMs, the types of drives in the array and their RAID configuration influence their performance characteristics. This matters because SQL writes to several different types of files using different access patterns - for example, transaction logs are sequential writes verses random reads/writes for data files. Separating different access patterns onto different storage arrays can speed up SQL I/O.
SQL Server Virtualization Disk Best Practice
Configuring the network for SQL VMs needs to consider not only I/O associated with the SQL application, but also workloads for VM maintenance - that is, failover, load balancing, migration, etc.
|SQL Server Virtualization Network Best Practice
Sizing SQL Servers in a virtualized infrastructure is a very different exercise compared to sizing in a physical infrastructure. It is important for SQL DBAs to work closely with IT Operations teams as operations has the best understanding of the physical attributes and performance of host, storage, and network resources and how differences between physical and virtualized resources affect server performance. However, IT Operations needs input from DBAs to ensure they know the resources required by SQL Server for it to function properly.
Ultimately the key is to understand the capabilities and physical limitations of the virtual infrastructure. Only then can IT properly assign the resources to support workloads for both SQL Server and any other VMs on the same host: