Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links


SQL Server DBA Jobs

Physical versus Virtualization for database servers

20 April,2011 by Tom Collins

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


Performance Counters Checklist


Performance Monitor

% Processor Time


Process / % Processor Time


Process/  Private Bytes/Virtual Bytes


Memory - Avail MB  and Pages/sec


Paging File / Percent used






Current Disk queue Length








SQL Counters

Transactions/sec by DB


MSSQL$XXXXXXXXXXX: Buffer Manager Page Life expectancy


MSSQL$XXXXXXXXXXX: Memory Manager Target Server Memory (KB)


MSSQL$XXXXXXXXXXX: Memory Manager Total Server Memory (KB)






VM  Counters



CPU Ready Time


Disk Read Rate


Disk Write Rate


Disk Commands Issued


Memory Consumed


Memory Balloon


Memory Swapped


Memory Swapped In


Memory Swapped Out




Hypervisor Host

CPU Usage in MHz


CPU Usage %


Disk Command Latency


Disk Write Latency


Disk Read Latency


Disk Commands Issued


Memory Consumed


Memory Balloon


Memory Swapped


Memory Swapped In


Memory Swapped Out



Related Posts

SQL Server – virtualization - is it a viable alternative

Database Virtual Server Candidacy Criteria

Author: Tom Collins (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Physical versus Virtualization for database servers | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer