How are people using virtualisation with SQL Server? Part 2

This is part two of an article reviewing the results of a virtualisation with SQL Server survey I performed.  Part one can be found here.

How do you size a new virtual server?
When deploying a new virtual server you want to size it according to its predicted workload knowing that additional resource can be allocated as required. Unlike physical servers giving your virtual server more resource than it actually needs can actually be a bad thing, if nothing else if you’ve got resource you’re not using your virtualisation admin is going to spot that and take it back. It was no surprise then that most people started off with a medium sized virtual server (2 vCPUs, 6GB memory) and scaling up or down as required. This was also a good test to see what spec of virtual server people were deploying. Most people started with mid-sized as mentioned previously, however 7 started with just 1 vCPU and 4GB of memory, while only 2 started at a high spec of 4 vCPU and 16GB.


Good vs. Bad Contention

One of the benefits of virtualisation which businesses like is “good contention”, the assumption that a physical server is never 100% CPU utilised so you can consolidate multiple physical servers onto a single one. But did anyone stop to consider the memory and storage availability vs. utilisation? Low workload servers are great candidates for virtualisation; however a production SQL Server probably doesn’t fit that category. Ensuring the benefits of virtualisation are experienced while making sure SQL Server has the resources and performance it requires is an art, which is often over-looked. What I call “bad contention” is when the sum of a resource allocated to all the virtual servers exceeds the amount the host server actually has. For example, I can allocate 8GB of memory to 3 virtual servers running on a host which only actually has 16GB. If all 3 servers really do need 8GB at once then we have bad contention occurring, the missing 8GB has to be found using clever tricks the hypervisor knows about which might have good intentions but might cause you to gulp if you weren’t expecting them Bad contention doesn’t just affect memory, it can affect every resource a virtual server requires, CPU, memory, storage I/O and network I/O.

 

Attitudes towards contending resources/over-allocation

Now we understand how “bad contention” can be a bad design practice it’s interesting to see people’s attitudes towards it.

 

Virt_05

 

A few people are happy to contend CPU and memory, I suspect that ties in with the heavy use of virtualisation for dev/test environments where performance isn’t key. What’s interesting is that most people said they deliberately don’t contend any resources. Wow! To me that means one of three things: people start off with good intentions but reality changes that, people are deploying virtualisation in really high end solutions or what I suspect is the case, people don’t realise how virtualisation contends resource – for example do people really have 2 HBAs in the host server for every VM which requires SAN? This is an area I’ll go into in future articles.

 

The final of this article, part three, is here.

Advertisements

One thought on “How are people using virtualisation with SQL Server? Part 2

  1. Pingback: How are people using virtualisation with SQL Server? Part 1 | Gavin Payne

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