Physical versus Virtualization for database servers

In the world of database servers , physical servers have many advantages. But increasingly, there is pressure to migrate database servers onto a virtual environment.

This is due to perceived inventory management and disaster recovery \ high availability advantages, in which there are many.  

My argument is that we're already virtualizing via managing multiple instances on one server – with all the related benefits. Therefore, strong evidence is required, backed up by thorough acceptance testing. 

These are some of the issues and analysis required in making the decision to host database servers on a virtual environment

Firstly ,in comparison to physical servers, the added layer is between the OS and the hardware i.e the hypervisor. The hypervisor , aka Virtual Machine Manager (VMM)  is the program managing multiple OS on the same physical host. From the OS perspective the appearance is of autonomy in CPU & memory. In fact, the hypervisor is controlling the allocation of resources. Examples of hypervisors are – Microsoft Hyper-V, VMware ESX

 This leads to the question, if the host boots up with multiple guests, what are the other guests doing? More below

Memory checklist

What is the guest to host ratio? How much physical memory on host relative to total memory allocated to guests?

Is there de-duplication? What is the likelihood that SQL Server will have the same pages stored in memory – such as buffer cache? Very slim as different data sets are used. SQL Server has a builtin clean up mechanism to clear out data sets that haven’t been used for awhile.the main purpose of de-duplication is to maintain one copy of patterns of data.

Balloon driver – claiming memory .The purpose of the  balloon is to  decrease pressure on host , but how does this impact on SQL Server?. SQL Server manages its own memory – and only requests more memory , if it thinks more memory is available.

If hypervisor supports. Set a memory reservation and prevents the balloon driver from taking memory

Avoid SSMS on Production server , as this encourages users to RDP onto servers . Ultimately , executing SSMS on a server – will consume resources.

 SQL Server memory configuration settings 

Storage checklist

How many fibre channels to SAN?

Raw Luns or Virtual file? If you need the extra 10% then don’t virtualize

One of the other issues with virtualizing , is how compatible with Failover Clustering. For example, MSCS clustering with VM option does not allow vMotion.

SAN snapshot – do they work  with files on different drives? Vendors , such as IBM offer groupings – whereby drives can be grouped.

How may Network cards and  are the setting suitable for relevant network traffic ?

CPU checklist

How fast is each physical core ? What is the CPU count?

Is the hypervisor placing a limit on CPU speed?

Is there throttling? This is a central issue when it comes to analysing CPU usage on a virtual server. The CPU % used may show 100% usage – but that is 100% of what is allowed by the hypervisor. Factors influencing CPU availability are : other guests and throttling settings

Share cache or cache per CPU? If the hypervisor is managing multiple sockets – then the CPU cache is distributed across multiple sockets  - the consequence , cached data is lost

What hypervisor is being used? Different hypervisors have different default settings

Uninstall unnecessary virtual hardware e.g cd drivers

Remove all background services- such as screen savers and antivirus tools. If they are scheduled at the same time on guests – this will lead to a decrease in performance during that time – and ultimately SQL Server Query response times will suffer

Monitor  for throttling .Other guests on the same host. Intel – such as “Sandy Bridge” processors, throttle

Avoid affinity masking – under virtualization. Typically a DBA will monitor CPU usage – and attempt to manipulate such configurables as affinity masking – the problem is the DBA does not have a full picture of what is ocuuring. As mentioned above – CPU % on the server does not represent an accurate picture of what is occurring . Don’t use affinity masking.

According to BOL – affinity masking will be removed in future versions. The main purpose of affinity masking in SQL Server is to control dynamically,

Monitoring checklist

Seeing 100% usage – only means I’m using 100% of what I can get!

What is the hypervisor doing?

Current resource usage of the virtual server?

Current resource usage of the underlying host?

What are the virtual host guest distribution?

How do you monitor the host ? I will post in another blog my powershell scripts that allow the monitoring of the host

See Performance Counters Checklist below

Troubleshooting methodology in virtualization

When approaching a database server performance problem on a virtualized platform  the approach should be from the hardware and up. This will require some tools to consistently analyse  :

Hardware (LUN,Disk,Storage Controller( Cache),Fibre Channel HBA

Hypervisor (resource management, other guests)

CPU Cores

OS – Windows

Cache – SQL Server Read Ahead Rate

SQL server

Table Indexes

Query

Performance Counters Checklist

Related Posts

SQL Server – virtualization - is it a viable alternative

Database Virtual Server Candidacy Criteria