SQL Server performance in a virtual world

  • Article

Despite doing quite a few posts and talks on Virtualising SQL Server, I am still getting the odd comment on earlier posts, so please forgive this if you have heard it all before.

Firstly these are my musings on the topic to date:

The post I want to revisit is performance, and Microsoft have put a lot of work into exposing its testing of SQL Server on Hyper-V :

  • the SQLCAT (Customer Advisory Team) whitepaper on virtualising SQL Server (there’s also one for Reporting Services here)
  • This TechNet whitepaper on running High Performance SQL Server workloads on Hyper-V  2 (the version in Windows Server 2008 R2)
  • Microsoft has 5,000 plus virtualised instances of SQL Server as per this TechNet White Paper on Green IT

There has also been some work done by Vmware in this space, and a quick search should find that for you.

However if you don’t completely trust all of these resources and you want to determine if SQL Server virtualisation works for you:

Establish a control or baseline.  Typically this will be a an analysis of an instance of SQL Server running on a physical server.  How you do this will depend on which version of SQL Server you are running; if you have SQL Server 2008 turn on data collection in management studio, if it’s an older version then I can only suggest timing your longest/most important running queries.

Compare like with like.  Match the characteristics of your physical server in the virtual machine, for example the same number of cores/virtual processors, the same RAM, the same network bandwidth.  Given that IO is often a bottleneck for SQL Server matching this in a virtual machine is an interesting exercise and will depend on your infrastructure:

  • If you have your databases on a SAN, all you should do in the VM is have a pass through disk(s) to reference that same SAN.
  • If you have directly attached storage, then is the physical disk on the server running your SQL Server VM as fast as on the control machine.?You also need to be mindful of what other VM’s are contending for that disk while you’re testing SQL Server.

Rerun your tests on the virtual machine and compare the results. One thing to note here is that there should be no DB tuning or anything done in the VM to optimise performance as that could also be done on the physical machine and void your evaluation. Having said that things like compression in SQL Server will possible have more of a benefit on a VM than on a physical server as you sacrifice some CPU for less IO.

Let’s assume for the sake of argument that the answer comes out that you have 80% of the performance you had on the physical server (the reality should be more like 95%). What are you getting for this 20% loss in performance? Basically manageability and control by removing your dependence on a particular server. Is that worth paying for? Only you can answer that.  For many organisations this is a price worth paying and often have a virtual first policy requiring stakeholders to justify using physical servers.

But virtualisation is more than that, it’s also about consolidation. Your new server running those VM’s is designed for virtualisation whether you use it for that or not. It’s going to use less power for a given workload than the one you bought four years ago and its probably going to have more cores than before.  So in addition to running your test VM at our arbitrary rate of 80% of physical performance it will also run maybe four or five other VMs. 

If you take virtualisation as simply a way of saving money through better use of hardware and power, the savings are verifiable from Microsoft, from Citrix, from Vmware.  If this was all smoke and mirrors I wouldn’t be blogging about it and Vmware who have relied on this as their main revenue stream would be out of business. 

Does it work for SQL Server? I think so for all but the largest implementations. Microsoft thinks so too, and so do most of the Vmware community I know, so if you disagree show me the evidence and we’ll discuss it over a pint, over coffee,  or over the internet.