Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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 '11 at 14:53
    
No, not local. But I don't know the details of the SAN. –  JerryOL Mar 5 '11 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/… –  Terry Bochaton Mar 13 '11 at 8:53
    
@Terry, in the future feel free to flag such an answer and we'll convert it for you. –  jcolebrand Mar 16 '11 at 4:59

2 Answers 2

up vote 10 down vote accepted

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 performance 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 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) of 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.
share|improve this answer
1  
Well, yes and no. An SAN volume mounted into the VM via an iSCSI initiator will be pretty damn quick! –  Gaius Mar 4 '11 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. –  David Spillett Mar 5 '11 at 11:57

Here's a good place to start: http://www.brentozar.com/sql/virtualization-best-practices/ and http://sqlskills.com/blogs/brent/category/Virtualization.aspx

share|improve this answer
1  
~ Are there any parts of that that you might want to highlight from the articles? –  jcolebrand Mar 4 '11 at 14:52
2  
No. Since virtualization can be a complex topic, I would advise to read it all. It's not really that much. If the OP is looking for a quick answer, it probably wont be the correct answer. –  Eric Humphrey - lotsahelp Mar 4 '11 at 19:04
    
I was after best practices in general. Having just moved SQLServer 2000 Standard to a new VM, I'm trying to resolve issues with web sites calling stored procedures that now time out if they are not cached in memory; even though the physical and VM assigned memory is the same, 4GB. –  JerryOL Mar 5 '11 at 2:10
1  
@JerryOL: Make sure that the RAM allocated to the VM is not permitted to be paged out (otherwise allocating 4Gb to the VM is not the same as having 4GB on the physical machine) and if the virtualisation solution you use offers different virtual controller types makes sure the best one is picked and the right driver is used by the guest OS. –  David Spillett Mar 5 '11 at 11:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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