Top 5 SQL Server Virtualization Mistakes to Avoid

Don’t repurpose older servers –It might be tempting to try to try to gain some life for your older servers by using them as virtualization hosts. However, older systems don’t typically have the processing power of today’s multi-core processors. Older systems also may not support Second-Level Address Translation (SLAT), which provides a big virtualization performance boost by enabling the CPU to maintain the mapping between the virtual memory used by the VMs and the physical memory in the virtualization host. If this memory mapping task isn't performed by the CPU, then the hypervisor has to perform the mapping—taking CPU cycles away from all of the other VMs.

Don’t overcommit the host processors – There is nothing to stop you from configuring far more virtual CPUs than you have physical cores. However, if you do that, it’s quite possible to overcommit your physical CPUs where they will have to time slice processor cycles among many running VMs. For optimum performance, you should try to reserve one physical core for each virtual CPU in your VM. This will help ensure the VM has physical processing power if it needs it.

Don’t undersize virtual machine RAM – The availability of host RAM is a limiting factor to how many VMs you can simultaneously run on a virtualization host. You might consider limiting the virtual RAM in your SQL Server VMs in order to achieve higher VM consolidation ratios. However, SQL Server performs far better when it has adequate amounts of RAM, as SQL Server will grow its Buffer Pool in response to growing workloads. It’s also typically recommended that you enable dynamic memory for VMs running SQL Server Enterprise edition. The Hot-Add RAM capability in Enterprise edition enables it to dynamically add memory to a running VM.

Don’t use the VM default virtual hard disk configuration -- The default settings for a virtual machine creates a new VM with a single virtual hard disk. Using this configuration would result in your operating system, your SQL Server instance, and your data and log all stored on the same virtual hard disk.  Almost all production workloads would immediately run into I/O contention problems. Instead, you should split your OS, data and log files onto separate virtual hard disks that are served by different physical drives or LUNs.

Don’t use dynamic virtual hard disks – By default, Hyper-V creates dynamic virtual disks for new VMs. However, using a fixed virtual hard disk is the best choice for virtualized SQL Server systems that run a production workload. Dynamic virtual hard disks use less disk space than fixed virtual disks, but they do not provide the same level of performance. Dynamic virtual hard disks are a good choice for development and test environments or noncritical production workloads. Fixed virtual hard disks are better for production because dynamic virtual hard disks can experience occasional pauses when the dynamic disk needs to be extended.