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