DaveWentzel.com            All Things Data

Advanced tips for a better performing virtual SQL Server

Here are some tips for a better performing SQL Server vm.  Note that almost every tip involves disk, which should be no surprise since rotational media is the slowest component of any system since it is the only component with moving parts:

  1. raw LUNs can help if you really have I/O pressure.  (Converting a virtual disk into a Raw Device Mapping)
  2. For Hyper-V solutions, never use dynamic VHD's since the file is not actually allocated on the host and therefore must be allocated on the fly.  This is never a good idea and is similar to autogrowth of a SQL Server data file.  It's performance-draining and always happens at the worst possible time.  The better solution is pass-through disks in Hyper-V.  A pass-through disk is storage (using a SAN LUN) that is mapped to the root partition (or host) but is in an offline state (it's actually brought online, then initialized, then taken offline again).  It is then available to the guest as a pass-through disk, giving that single guest exclusive access.  This also means there is no theoretical size limitation to a pass-through disk.  This makes monitoring a little more difficult at the root partition level since the logical disk counters won't be available (but the physical disk counters will be).  
  3. Whenever monitoring IO performance of a virtual SQL Server prefer monitoring on the host to monitoring on the guest.  Microsoft's own analysis is here.  
  4. For the best I/O characteristics prefer raw LUNs first, then pass-through disks, then fixed VHDs, then, lastly dynamic VHDs.  
  5. Avoid using emulated devices and instead use synthetic devices.  An emulated device "emulates" a device through software in the child partition, meaning that there is good compatibility, but at the expense of more CPU cycles to do the emulation.  Synthetic devices are proxies for real resources on the host so their job is merely to forward I/O around, meaning less host cycles are spent on I/O.  
  6. Set the minimum memory reservation on the VM and don't use memory ballooning.  Memory ballooning (overcommitting memory) is thought to be terrible and should never be used by many.  That's just wrong.  In many cases VMs will never use the amount of RAM granted to them on the host.  In those cases that RAM is being wasted, so why not overcommit?  I am simply saying here that if performance is your goal for your virtualized VM, then consider not using ballooning or restrict it appropriately.  (But of course RAM is cheap, why not just buy more?)
  7. CPU overcommitment should also be avoided if performance is paramount to cost/vm.  CPU overcommitment is conceptually similar to memory overcommitment, and obviously it will require additional CPU cycles on the host to implement.  
  8. When possible, do your system monitoring from the host, not the VM, to assure you aren't seeing bogus metrics.  Also, certain things like CPU q'ing will really only show up on the host, not the VM.  

More available on the Performance Page

Add new comment