Although virtualizing SQL Server was once unheard of, today it’s pretty common. However, not all virtualized SQL Server instances are implemented in the best way. In fact, if you follow the New Virtual Machine wizard for either Microsoft Hyper-V or VMware vSphere, you’re sure to end up with a less-than-optimal virtual SQL Server instance. Here are some tips I consider essential for optimizing your virtualized SQL Server instances.
Related: SQL Server Virtualization FAQs
1. Use SLAT-enabled 64-bit processors on the host.
The first, and one of the most important SQL Server virtualization tips, is to be sure your virtualization host is using the right processor. Although 64-bit processors are definitely a must-have, not all 64-bit processors support Second Level Address Translation (SLAT). Older servers in particular might not have SLAT support. SLAT moves the job of mapping virtual memory in the virtual machine (VM) to the host’s physical memory from the hypervisor to the CPU itself. SLAT enables higher VM performance and scalability.
2. Keep a one-to-one relation between cores and virtual CPUs.
While not strictly required, having a one-to-one ratio ensures each VM will continually have available processing power. Available processing power is required for resource-intensive production workloads such as SQL Server.
3. Take advantage of dynamic memory.
To take advantage of dynamic memory, you need to be using the Enterprise Edition of SQL Server 2008, 2008 R2 or 2012, or the SQL Server Datacenter Edition of SQL Server 2008 or 2008 R2. In addition, the VM guest OS needs to support hot-add RAM. Hot Add RAM is supported by Windows Server 2012, 2008 R2 SP1, and 2003 R2 SP2 Enterprise and Datacenter editions. When a SQL Server workload causes the sqlserver.exe process to grow, the SQL Server database engine will detect the added memory and grow its buffers to meet the workload demand.
4. Use fixed virtual hard disks.
The fixed virtual hard disk (VHD) is the best choice for virtualized SQL Server systems that run a production workload. Dynamic VHDs are a good choice for labs, test environments, or noncritical production workloads. Dynamic VHDs use less disk space than fixed virtual disks but they don't provide the same level of performance. Workloads running on dynamic VHDs can experience occasional pauses when the dynamic disk needs to be extended. Differencing disks are really best suited for lab environments where disk space is at a premium. They use far less storage but they also have much lower levels of performance. Pass-through disks are an option for workloads with the highest I/O requirements, but they don't have the flexibility of fixed VHDs.
5. Separate your OS, data, and log files.
If you accept the default configuration that’s offered by either VMware VSphere or Microsoft Hyper-V, you’ll end up with a poor performing virtual SQL Server instance. The default configuration uses a single VHD for storage. Most production workloads with higher transaction rates would immediately run into disk contention problems. For production virtual SQL Server instances you should separate your OS, data file, and log files on to different VHDs or pass-through disks. It’s important that you be aware of the physical disk implementation and make sure that the disks used for the guest OS and the SQL Server data and log files use separate spindles.
Learn more: Let's Talk Licensing and Virtualization for SQL Server
