Measuring SAN Performance with Avg Disk Transfer and disk bytes per second

23 June,2014 by Jack Vamvas

Question: Using Perfmon counters is it possible to estimate whether it’s the SAN or SQL Server  creating the bottleneck?

The SAN administrator is reporting no delays on the SAN, and we need to feedback some useful information . He’s indicated the SAN should be able to handle > 160 mb per second. The question is whether the SAN is experiencing issues or the SQL Server is driving to much traffic

Answer: The Perfmon counters Avg. disk sec/transfer and disk bytes per second can supply a good indicator .

Avg.disk sec/transfer – (from MSDN) “Perfmon captures the total number of individual disk IO requests completed over a period of one second. If the Perfmon capture interval is set for anything greater than one second, the average of the values captured is presented”

Disk bytes per second – (from MSDN)  “Perfmon captures the total number of bytes sent to the disk (write) and retrieved from the disk (read) over a period of one second.

 

First check the Avg.disk sec/transfer transfer rates. I follow these guidelines:

SQL Server Storage and IO performance

In summary,

Less than  .010 is excellent

Less than .020 is good

Less than .020 - .025 is satisfactory

Greater than .025 is worth investigating , with potential issues as the number grows

If you think there are issues, the question to ask is if there is a disk subsystem issue or SQL Server is causing excessive disk  traffic flooding.

To calculate if SQL Server is causing the flooding use Disk bytes per second. Speak to your SAN administrator about the throughput capacity in MB\sec.    If the SAN can handle 160 mb/sec, and the disk bytes per second is greater than 160 mb/sec , with the SQL Server being the main driver – than there is a high probablility SQL Server is flooding the disk.

Figure out a course of action to decrease the pressure.

Related Posts

SQL Server measure i\o transaction rates and sys.dm_io_virtual_file_stats

SQL Server – RAID levels overview

SQL Server IO patterns and RAID levels


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

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 on Measuring SAN Performance with Avg Disk Transfer and disk bytes per second


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