What Do I Need to Know to Run SQL Server on VMWare Virtualized Environments?
Can SQL Server Be Run on a Virtual Machine?
Yes! SQL Server can run very well on a virtual machine (VM) but SQL is still resource-intensive. Many companies turn to IT service providers for insight into their particular environment. If you do decide to virtualize your SQL environment, it’s important to implement best practices. Not adhering to best practices can compromise the performance of your virtual SQL Server. It’s usually worth the effort since virtualization can improve your organizations ROI through consolidating servers, eliminating the need for new hardware for each application, and increasing availability.
How Does Virtualization Impact my Licensing Requirements?
A well-planned virtualization engagement consolidates SQL servers to minimize the number of required SQL licenses, which can save a significant amount of money.
The main impetus behind SQL Server virtualization is the ability of internal or third-party IT resources to scale up quickly to accommodate new team members and applications. 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.
How Can I Optimize CPU Performance?
SQL Server should not have to contend for resources alongside other VMs, so make sure the host’s CPU resources aren’t overallocated. Significant wait time for CPU may cause latency issues that impact all applications on the SQL Server. If the vCPUs allocated to the VMs are too high, users experience a high CPU Wait Time Per Dispatch or CPU Ready status.
For SQL Server VMs with multiple Non-Uniform Memory Architecture (NUMA) nodes, be sure to enable virtual NUMA. Evenly distribute CPU and Memory assignments among your physical NUMA nodes. When virtual NUMA is disabled, you can configure VMs with vCPU resources and additional memory within the NUMA node. Many practitioners have better performance on multiple SQL VMs within a NUMA node. Performance suffers on larger SQL VMs across NUMA nodes that have disabled virtual NUMA.
Here are more CPU best practices for SQL virtualization:
- Don’t overcommit the memory on your SQL VMs hosts
- Use capacity planning to figure out the amount of memory is in use
- Adjust VM memory allocations across applications based on workload and priority
- Memory reservations provide a fixed amount of memory for SQL VMs as they power up. However. Hyper-V doesn’t require reservations for minimum memory allocation.
SQL Server Virtualization Memory Best Practice
Do not overcommit memory on hosts running SQL VMs
Use capacity planning to determine how much memory is in use and adjust VM memory allocations based on workload.
In VMware, memory reservations can ensure that a SQL VM has a fixed amount of memory when it powers on. Reservations are not required to ensure minimum memory use in Hyper-V.
What About Optimizing Disk Performance?
There are three main considerations for optimizing disk performance: SSD vs. HD, load balancing and redundancy.
- Solid State Drives vs Traditional Hard Drives. SSDs connected via NVMe deliver I/O rates several magnitudes better than hard drives. Evaluate the workload to decide whether the I/O rates you need would best be served by SSD or HD technology. Fortunately, SSDs are much more affordable now than a few years ago.
- Load balance across storage devices. Watch out for the I/O rate on storage devices to ensure they can handle the overall I/O load of the VMs they support. Files can be moved to other storage devices for load balancing.
- Redundancy. Drives with redundant arrays can provide insurance against disk failure. However, redundancy may also result in performance penalties. RAID 10 disks with high read/write rates are recommended by Microsoft. RAID 5 disks work well for read-only databases.
What Are the Main Network Considerations?
Paravirtualized network adapters such as VMXNET3 deliver better performance than emulated adapters. Jumbo frames that transfer 9,000-byte data packets perform better than standard 1,500-byte Ethernet frames. Enable jumbo frames on storage traffic channels like iSCSI or VM migrations. Use fewer large data transmissions to enhance network performance and reduce CPU load; replicate optimal settings between endpoints.
