This section provides a summary of best practices for virtualizing SQL Server with VMware. This information is based on the latest VMware guidance. For more information, see Architecting Microsoft SQL Server On VMware vSphere—Best Practices Guide.
Processors (CPU)
For SQL Server to run efficiently on modern hardware, the SQL Server Operating System (SQLOS) must have a full understanding of hardware layout. With the advent of multicore and multinode non-uniform memory access (NUMA) systems, understanding relationships among cores, logical CPUs, and physical CPUs has become important.
VMware uses the following terms for physical hardware:
- Physical CPU or physical socket—A physical CPU installed in the server hardware.
- Physical core—An independent processing unit residing on a single processor or physical CPU
- Logical core—A logical processor on a physical core with own processor architectural state. Intel Hyper-Threading technology is the most widely deployed example of logical cores.
The corresponding VMware virtualization terminology is:
- Virtual socket—Represents a virtualized physical CPU and can be configured with one or more virtual cores.
- Virtual core—Equal to a CPU and viewed by an operating system as a separate processor unit.
- Virtual CPU (vCPU)—Virtualized central processor unit assigned to a VM. Total number of assigned vCPUs to a VM is calculated as:
Total vCPU = (Number of virtual sockets) * (Number of virtual cores per socket)
Consider the following information about vCPUs when using VMware virtualization with SQL Server:
- When consistent performance is high priority, VMware recommends that the total number of vCPUs that are assigned to all the VMs not exceed the total number of physical cores available on the ESXi host machine.
- You might be able to increase the number of allocated vCPUs if monitoring suggests that unused CPU resources are available.
- On systems with Intel Hyper-Threading enabled, the number of logical cores (vCPUs) is twice the number of physical cores. In such case, do not assign the total number of vCPUs.
- Lower-tier SQL Server workloads are, by definition, less impacted by latency variability. Therefore, these workloads can be run on hosts with greater ratios of vCPUs to physical CPUs.
- The vSphere CPU scheduler policy is designed to balance maximum throughput and fairness between VMs.
- Reasonable levels of CPU overcommitment can increase overall system throughput, maximize license savings, and continue to maintain adequate performance.
- Intel Hyper-Threading generally improves the overall host throughput by 10 to 30 percent, which suggests a 1.1 to 1.3 ratio of vCPUs to physical CPUs.
- VMware recommends enabling Hyper-Threading, when available, in the UEFI BIOS so that ESXi can take advantage of the technology.
- ESXi CPU management is aware of when Hyper-Threading is enabled; therefore, for example, a VM with two vCPUs is mapped to different physical cores instead of to two logical threads on the same physical core.
- VMware recommends that you perform thorough testing and monitoring when you are using Hyper-Threading for SQL Server workloads.
Memory
Almost all modern servers use a non-uniform memory access (NUMA) design for communication between main memory and the CPUs. NUMA is a hardware architecture for shared memory, implementing subdivision of physical memory bunks between physical CPUs. A NUMA node is defined by one or more CPU sockets together with a block of dedicated memory. A single CPU socket and connected memory is a NUMA node.
During the past decade, NUMA has been a much-discussed topic. The relative complexity of NUMA is driven, in part, by different vendor implementations. In virtualized environments, the complexity of NUMA is also driven by the number of configuration options and layers that must be traversed—from the hardware through the hypervisor to a guest operating system, and, finally, to the SQL Server application. A good understanding of NUMA hardware architecture is a requirement for any infrastructure architect or SQL Server DBA working with a virtualized SQL Server.
To achieve better efficiency on servers with high core counts, Microsoft introduced Soft-NUMA. Soft-NUMA enables the partitioning of available CPU resources within one NUMA node into multiple “Soft-NUMA” nodes. According to VMware, Soft-NUMA is compatible with the VMware virtual NUMA (vNUMA) topology and might further optimize scalability and performance of the Database Engine for most of the workload.
Consider the following information about memory resources when using VMware virtualization with SQL Server:
- Monitor VMs to detect a lack of memory resources for the SQL Server Database Engine. This issue induces Windows Server to page memory to disk, resulting in increased disk I/O activities and poor performance.
- When designing for performance, prevent memory contention between VMs by avoiding overcommitment of memory at the ESXi host level.
- Consider checking the hardware physical NUMA memory allocation to identify the maximum amount of memory that can be assigned to a VM without crossing the physical NUMA boundaries.
- When achieving adequate performance is the primary goal, consider setting the memory reservation equal to the provisioned memory. This setting adjustment eliminates the possibility of ballooning or swapping and guarantees that the VM gets only physical memory.
Virtualized storage
Configuring storage in the virtualized environment requires knowledge of the storage infrastructure. As with NUMA, it also requires an understanding of the coordination between each level in the I/O path—in this case, from the application running in a VM to the physical reading and writing of information on a persistent storage medium.
vSphere provides the following options for configuring storage, all of which have useful applications in a SQL Server implementation with the Unity XT array:
- VMware Virtual Machine File System (VMFS)
- VMFS is the most widely used datastore on block storage systems such as Unity XT.
- The Unity XT array is the bottom layer, consisting of physical disks presented as logical disks (volumes) to vSphere.
- The Unity XT volumes are formatted as VMFS volumes by the ESXi hypervisor.
- VMware administrators create one or more virtual disks (VMDKs) that are presented to the guest operating system.
- Raw Device Mapping (RDM)
- NFS
- For NFS-based storage for ESXi, Dell EMC recommends VMware NFS datastores instead of general-purpose NFS file systems.
- A VM running SQL Server and using VMDKs on an NFS datastore is not aware of the underlying NFS layer. The guest operating system views the VM as a physical server running Windows Server and SQL Server.
- Shared disks for Failover Cluster Instance configurations are not supported using NFS datastores.
- VMware vSphere Virtual Volumes (VVols)
- The best practices and guidelines for using a VMFS datastore also apply when VVols are used as the underlying technology.
- VVols offer finer control at the VM level, independent of the underlying physical storage representation (volumes or file systems, for example).
- Storage operations are available with individual VM granularity, providing native array-based data services such as compression, snapshots, deduplication, encryption, and replication.
- Array-based replication with VVols is supported beginning with VVol 2.0 (vSphere 6.5).
- A VVol disk can be used instead of an RDM disk to provide a disk resource for a SQL Failover Cluster Instance beginning with vSphere 6.7 with support for SCSI-3 persistent reservation.
Virtualized networking
Networking in the virtual world follows the same logical concepts as in the physical world but uses software rather than physical cables and switches. The impact of network latency on SQL Server workloads can vary greatly. Monitoring networking performance metrics on either the existing workload or a well-implemented test system for a representative period helps with the virtual network design.
Consider the following networking information when using VMware virtualization with SQL Server:
- Both standard and distributed virtual switch types provide the functionality required by SQL Server.
- Use VLAN tagging and virtual switch port groups to logically separate management, vSphere vMotion, and network-based storage traffic.
- VMware highly recommends enabling jumbo frames on the virtual switches where you have enabled vSphere vMotion traffic or iSCSI traffic.
- In general, follow the networking guidelines for guest operating systems and hardware.