Today’s hypervisors like Hyper-V and vSphere make it easy to virtualize servers like SQL Server. However, they can also make it easy to create poorly performing virtualized SQL server instances. In this article I’ll cover some of the biggest mistakes that you can make when virtualizing SQL Server. Some of the top mistakes to avoid when virtualizing SQL Server are:
- Don’t virtualize on older servers – The older generation of processors don’t have the same type of processing power that modern multi-core CPUs possess. Today’s modern processors all support a feature called Second-Level Address Translation (SLAT) which enables the CPU to maintain the mapping between the virtual memory used by the VMs and the physical memory that’s in the virtualization host. If this memory mapping task isn’t performed by the CPU then the hypervisor has to take on that extra work which decreases the performance of the VM and takes CPU cycles away from all of the other VMs that are running on the host. SLAT enables that virtual memory mapping to be performed in hardware by the CPU providing better performance and scalability.
- Don’t overcommit the host – As a general rule of thumb for optimum performance you should try to reserve one physical core for each virtual CPU that you have configured. However, there is nothing to stop you from configuring far more virtual CPUs than you have physical cores. In some situations that’s fine. For instance, file serving or small scale database workloads may not require much processing power but in cases where performance is important be sure not to overcommit the host’s processing capacity.
- Don’t skimp on virtual machine RAM – Host RAM is the single biggest limiting factor to how many virtual machines that you can run simultaneously on that particular host. It can be tempting to limit the RAM that’s used by your VMs in order to achieve higher server consolidation ratios. However, SQL Server is a memory intensive server application that typically performs far better when it has adequate amounts of RAM. In addition, it’s usually a good idea to enable dynamic memory for any virtual machines running SQL Server Enterprise edition. Enterprise edition’s support for Hot-Add RAM enables it to take advantage of dynamically added memory.
- Don’t use the default storage configuration — Just like a physical server, a virtual SQL Server instance’s disk configuration can make a huge impact on SQL Server performance. The default settings for a virtual machine creates a new VM with a single VHD. Using this configuration would result in putting your OS files as well as SQL Server data and log files all on the same VHD. This default storage configuration would be suitable only for small virtual SQL Server instances with low transaction rates. Most production workloads with higher transaction rates would immediately run into disk contention problems. A better practice is to split your OS, data and log files onto separate VHDs that are served by different disks or SSD drives.
Avoiding these common and easy to make virtualization mistakes will help to ensure that your virtualized SQL Server instances will meet your performance and availability objectives.
Leave a Reply