Sqlserver-dba.com

Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Virtualization – and database servers

20 April,2011 by Jack Vamvas

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

 

Performance Monitor

% Processor Time

 

Process / % Processor Time

 

Process/  Private Bytes/Virtual Bytes

 

Memory - Avail MB  and Pages/sec

 

Paging File / Percent used

 

Secs/Write

 

Secs/Read

 

Current Disk queue Length

 

Reads/sec

 

Writes/sec

 

 

 

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)

 

MSSQL$XXXXXXXXXXX : Cache Hit Ratio

 

 

 

VM  Counters

CPU MHZ Used

 

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: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Enjoy this post? Enter your email address for updates on new posts:

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

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

Working...
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.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer