SQL Server Virtualization Mistakes to Avoid

Server virtualization is something that is increasingly common in today’s data center environment. The drive to virtualize servers is mostly cost driven, but if you do it wrong it can actually cost you more time and money to properly resolve. I have been using virtualized servers to run several SQL Server instances, and some hard-learned lessons are articulated quite well in this article by Michael Otey.

  1. 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. – My thoughts are that if you want the best performance, start by having the best possible foundation of your virtualization platform as your company can afford.
  2. 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. – You can do this wrong, and see poor performance from day one, or it might take weeks to uncover a problem that you created on day one.
  3. 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. – More RAM is always better, and it is usually cheaper than you think so get as much as you can afford.
  4. 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. – There are some hard won and thoroughly tested configuration requirements for non-virtualized SQL Server drives, and these lessons should be carried into the virtualized environment.
  5. 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. – Performance over economy every time. Performance is measured in seconds, so anything that will make your instances perform better will add up into noticeable performance improvements.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s