See What we are Talking About!
Performance Considerations when Virtualizing SQL Server on VMWare
Kevin Richey, CommitDBA Senior DBA/Data Architect
November 3, 2015
In today’s SQL Server environment, converged architecture and smaller datacenter footprint require DBAs to virtualize their enterprise databases. Based on previous experiences and difficulties with virtualizing SQL databases, some DBAs are skeptical and cautious about moving SQL Server databases into the virtual world. What they know best is the physical world, and they know it WORKS. Why change a good thing?
From the DBA’s perspective, virtualization is not a question of “Do you want to do it?”, but a question of “When will you be required to do it?” When preparing for the inevitable, it’s important to learn the areas of VMWare that could pose performance issues. Knowing which roadblocks you may experience, and ways to remediate them, will make the switch to a virtual SQL Server environment successful. Listed below are four key areas to evaluate when virtualizing your SQL Server databases.
Many disk configuration options are available. Depending on your needs, you could choose raw-mapped LUNS (RDM) or VMFS. If you are setting up a SQL Server cluster on VMWare, then RDM disks are required. If you are relying on VMWare HA and DRS, then you should use VMFS. RDM setup and configuration will be very similar to a physical machine setup, while VMFS introduces an additional layer between the OS and raw disks. If you choose VMFS, you must work with your infrastructure team to ensure the disks are aligned with the underlying storage, and the block size is optimal. Do not use “thin provisioned” and “lazy zeroed”, as this requires additional I/O for each new page written to disk. Always use “thick provisioned” or “eager zeroed”. When the setup and configuration is complete, run disk performance tests with tools such as SQLIO and compare the results to a physical machine connected to a LUN on the same storage group.
Shared network traffic can often lead to a bottleneck. Multiple NICs can be used for redundancy, and to separate virtual machine traffic with VMWare tasks such as vMotion. Since you are consolidating physical machines, and sharing VMWare hosts, your network requirements will likely be greater. Prior to migrating to VMWare, run tests to determine requirements for network traffic. If you are using an IP based storage protocol, such as iSCSI, it must be on a dedicated NIC to avoid a bottleneck.
When calculating CPU needs for a virtual machine, it’s important to keep in mind the CPU overhead required by VMWare. VMWare’s hypervisor will sit between the virtual machine and the bare-metal hardware for resource scheduling, and it requires CPU cycles. It’s important to not over-allocate vCPUs compared to the pCPUs in the hardware. If you get into CPU contention with the hypervisor, it is a lose-lose situation and a context-switching nightmare. If your CPU utilization reaches 80% on the host, keep an eye on the “CPU Ready” metric in VMWare, as you may be reaching a CPU bottleneck.
Since SQL Server is designed to utilize all available memory, over-allocating RAM to the virtual machines will likely cause the VMWare host to swap memory pages to disk. This will cause your SQL Servers to suffer and likely cause a huge performance issue. When accounting for a VMWare host failure, you must have sufficient RAM to run the virtual machines from the failed host on the other hosts without over-allocating the RAM.