What are the best practices for running SQL Server in a Virtual Machine? My on-line transaction activities are very low, but there is a high amount of data processing for the purpose of providing reporting data to multiple web sites.

  • 2
    Where are the disks being stored? on a local 5400 RPM HDD? On a SAN with Fiber connections and 15k spindle disks? All VMs these days are about IO, CPU is nearly nothing in the consideration.
    – jcolebrand
    Mar 4, 2011 at 14:53
  • No, not local. But I don't know the details of the SAN.
    – JerryOL
    Mar 5, 2011 at 2:14
  • 2
    I cannot add comments as I do not have enough reputation yet, but [according to Microsoft][1] in 2008 R2 there is little performance difference between fixed and dynamic virtual disks. [1]: blogs.msdn.com/b/tvoellm/archive/2009/08/05/…
    – TerryB
    Mar 13, 2011 at 8:53
  • @Terry, in the future feel free to flag such an answer and we'll convert it for you.
    – jcolebrand
    Mar 16, 2011 at 4:59

1 Answer 1


As with physical machines IO is king. There will be a noticeable IO hit from virtualisation (how much of a hit depends on the technology you choose), so make sure that you do nothing that can exacerbate this and harm IO performance further.

  • Always used fixed size, not dynamic virtual disks, and ensure that the underlying drives/arrays are sufficient. Dynamic vdisks are less performant than fixed size ones.
  • If the data in the VMs is not mission critical, turn on the VM solutions ability to cache writes (effectively lying to the guest OS about when things are physically written to disk) for extra performance. This is useful for a development and testing machine where the data is easily replaced, or read-only replicas local to the master that can be recreated if they experience any trouble, but it is recommended that such options stay off for production and backup systems as they increase the chance of data loss (or worse, corruption) if the machine powers off unexpectedly.
  • Ensure that each VM has enough RAM dedicated to it such that the normal working set of each database fits into memory with a fair amount of room to spare to avoid unnecessary IO. Also, ensure that the hypervisor will not page out the VMs RAM at any time (for instance most VMWare products can do this, which allows you to run a larger set of VMs than you otherwise could on the same hardware, but the performance degradation can be massive - there is an option to tune or turn off this behaviour).
  • If the host uses RAID5 arrays, consider moving to RAID10. This will reduce the space available but remove the write performance problems that can be apparent with RAID5 (or 6).
  • For VMs that are likely to see a lot of IO activity, consider giving them their own drive or array, or only have them share their drive(s)/array(s) with data that sees low activity.

Of course if your entire DB (and everything else the VM is running) fits into the RAM allocated to the VM and it sees very little write activity, IO performance can be much less of an issue.

Edit: a few more points:

  • If using virtual disks make sure that the best controller type is used for performance. Some virtualisation solutions offer several virtual controller types for better guest compatibility and you might find some less efficient than others due to protocol limitations in the design they are emulating and limitations in the guest OS's drivers.
  • If your virtualisation solution offers specific drivers for the virtual drive controller, make sure your guest OS is using them - these can speed up bulk IO by reducing the number of memory-to-memory copies and host<->hypervisor<->guest context switches involved in some operations.
  • The two points above apply if you are using network storage such as simple SMB shares or iSCSI too: make sure that you are using the most efficient virtual network controller on offer and that your guest OS is using the recommended driver for it, otherwise both latency and bandwidth may be more limited than needs be by the virtualisation layer.
  • 1
    Well, yes and no. An SAN volume mounted into the VM via an iSCSI initiator will be pretty damn quick!
    – Gaius
    Mar 4, 2011 at 23:35
  • 1
    @Gaius: good point. I've added a note regarding vNICs and drivers, which are likely to be the source of extra bottlenecks in those cases. Mar 5, 2011 at 11:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.