One of the problems in organizations that implement SQL Server on a virtual machine is the lack of recognition of the virtualization which leads to the non-optimal implementation of this service on the virtual structure and then causes them to face improper performance. In this post, I want to talk briefly about how to design and implement SQL Server in a virtual machine to run on vSphere.
The following configuration and settings are base on personal experience and VMware best practices.
- Enable DRS functionality for a cluster hosting SQL Server workloads.
- Separate database cluster from other production clusters
- Configure the BIOS/UEFI Settings
- Enable Turbo Boost
- Set Power Management to “OS controlled”
- Disable all processor C-states
- Set Power Management of ESXi to High Performance
- Using multi-NIC vMotion for High Memory Workloads.
- Enable jumbo frames on the virtual switches where you have enabled vSphere vMotion traffic or iSCSI traffic.
Virtual Machine Configuration
- Do not enable CPU hot plug by default, especially for VMs that require vNUMA.
- Reserving all memory will disable the creation of the swap file and will save the disk space especially for VMs with a large amount of memory assigned.
- It is preferable to rely on rightsizing than on memory hot plug.
- Place SQL Server data (system and user), transaction log, and backup files into separate VMDKs and possibly on separate datastores. For the most critical databases where performance requirements supersede all other requirements, maintain 1:1 mapping between VMDKs and LUNs.
- Utilize the VMware Paravirtualized SCSI (PVSCSI) Controller as the virtual SCSI Controller for data and log VMDKs
- Placing OS, data, and transaction logs onto a separate vSCSI adapter optimizes I/O by distributing the load across multiple target devices and allowing for more queues on the OS level.
- Use the VMXNET3 paravirtualized NIC.
|Hard Disk 1||0:0||OS||LUN01|
|Hard Disk 2||0:1||SQL Binary||LUN02|
|Hard Disk 3||1:0||DB Files||LUN03|
|Hard Disk 4||1:1||DB Files||LUN04|
|Hard Disk 5||2:0||Temp DB||LUN05|
|Hard Disk 6||2:1||Backup DB||LUN06|
|Hard Disk 7||3:0||Temp Logs||LUN07|
|Hard Disk 8||3:1||DB Logs||LUN08|
Windows Sever Configuration
- Set the high-performance power management plan to avoid CPU park.
- Enable RSS (Receive Side Scaling) which enables distribution of the kernel-mode network processing load across multiple CPUs.
“netsh interface tcp set global rss=enabled autotuninglevel=normal“
SQL Server Configuration
- Configure maximum server memory and minimum server memory to define the range of memory for the SQL Server process to use.
- The recommendation for CTFP value from five to approximately 50 to make sure only large queries run in parallel.
- Set the MAXDOP according to Microsoft’s recommendation for the number of cores in the VM’s vNUMA node (no more than eight)
If you have any questions or comments please let me know.