Identify Disk Bottleneck in SQL Server using Perfmon Disk Counters

Disk I/O problems are some of the most difficult problems to diagnose and to debug. Here I am going to discuss about perfmon disk counters that will help us in diagnosing the disk related issues. I will segregate the values for all disk counters into three categories, Excellent, Good and Poor. Get your values for these disk counters and compare with these three categories to know how your disk is performing whether your disk is performing excellent, good or it’s required your intervention to improve its performance.

  1. Excellent – You Disk Subsystem is responding perfectly for respective counter.
  2. Good – You Disk Subsystem is responding fairly for respective counter and you can consider it good considering your workload and other resource utilization.
  3. Poor – You Disk Subsystem is not responding well for respective counter and it requires your intervention to drill down more to understand the reason behind slowness.

If you want to read about CPU and Memory related performance counters then i would suggest you to go through with below attached articles.

Below are the list of important perfmon disk counters that we will be discussing in this article.

  • Average Disk Sec/Read
  • Average Disk Sec/Write
  • Average Disk Sec/Transfer
  • Average Disk Queue Length
  • Current Disk Queue Length
  • Disk Read/Sec and Disk Write/Sec
  • %Disk Time
  • %Idle Time

Perfmon Disk IO Counters

Average Disk Sec/Read

Average Disk Sec/Read is important perfmon disk counter that shows the average time in seconds needed to read data from disk and better explain the disk latency. We can find these counters at the logical and physical disk object level. The value for this counter is generally the number of seconds it takes to do each read. The shorter the time needed to read or write data, the faster the system. If you constantly get this counter value more than 20ms then it means your disk is not performing well.

Your disk performance is:

  • Excellent, if the value of this counter is less than 6 milliseconds (0.006 sec)
  • Good, if the value of this counter is up to 15 milliseconds (0.015 sec)
  • Poor, if the value is constantly more than 15 milliseconds (0.015 sec)
Average Disk Sec/Write

Average Disk Sec/Write is also an important counter that shows the average time in seconds needed to write data to disk. that can better explain the disk latency. This counter can be tracked at the logical and physical disk level. The value for this counter is generally the number of seconds it takes to do each read. The shorter the time needed to read or write data, the faster the system. If you constantly get this counter value more than 20ms then it means your disk is not performing well.

Your disk performance is:

  • Excellent, if the value of this counter is less than 6 milliseconds
  • Good, if the value of this counter is up to 15 milliseconds
  • Poor, if the value is constantly more than 15 milliseconds.
Average Disk Sec/Transfer

Average Disk Sec/Transfer is a time that measures average latency for read or write operations. If the Average Disk Sec/Transfer value increases, this indicates that the I/O subsystem is not optimally keeping up with the I/O demand. This counter is available under Logical Disk and Physical Disk object. If you are keeping a watch on this counter make sure to have a look at above perfmon disk counters Average Disk Sec/ Read and Average Disk Sec/Write as well because Average Disk Sec/Transfer is the average of both read and write counters so you need to find out high value is due to read operation or write operation. Below are the values that will tell you whether your disk is performing excellent, good or it is performing slow and requires your intervention to improve its performance.

Your disk performance is:

  • Excellent, if the value of this counter is less than 6 milliseconds
  • Good, if the value of this counter is up to 15 milliseconds
  • Poor, if the value is constantly more than 15 milliseconds.
Average Disk Queue Length

Average Disk Queue Length counter shows you the average number of read and write requests that were queued on the selected physical disk. If this value is exceeding its threshold frequently then we should also look at other perfmon disk counters Average Disk Read Queue Length and Average Disk Write Queue Length. This way you can diagnose whether Average Disk Queue Length is high due to high read or high write operation. The recommended value for this counter is less than 2 per individual disk so if you have multiple disks then your recommended value for Average disk queue length would be twice the no of disks.

Be careful when using thresholds of 2 with SQL Server and SANs.  This value can be much higher than 2 if you have multiple disks.  Let’s say you have 6 drives and Average disk queue length counter is showing around 12, then you should consider your Average disk queue length value 9/6 = 1.5 not 9.

Your disk performance is:

  • Excellent, if the value of this counter is less than 1.5 per drive.
  • Good, if the value of this counter is up to 2 per drive.
  • Poor, if the value is constantly more than 2 per drive,
Current Disk Queue Length

Current Disk Queue Length is the number of disk requests that are currently waiting as well as requests currently being serviced. The difference between average disk queue length and current queue length is CDQL tells current load whereas ADQL tells load during a given time frame. The Current Disk Queue Length metric in Windows Performance Monitor is available for both physical and logical disk. The Current Disk Queue Length value should be less than 2 per disk.

Your disk performance is:

  • Excellent, if the value of this counter is less than 1.5 per drive.
  • Good, if the value of this counter is up to 2 per drive.
  • Poor, if the value is constantly more than 2 per drive.
Disk Read/Sec and Disk Write/Sec

Disk Reads/Sec and Disk Writes/Sec show you the number of read and write operations happening per second on the disk. If you want to set the benchmark for disk capacity then you can determine it by gradually increasing the load on the system.

Your disk performance is:

  • Excellent, if the value of this counter is less than 75% of total disk capacity.
  • Good, if the value of this counter is up to 85% of total disk capacity.
  • Poor, if the value is constantly more than 85% of total disk capacity.
%Disk Time

%Disk Time counter tells how busy the disk is in performing read and write operations. You can see its values normal even your disk has serious disk performance issue. You should compare its value with current/average disk queue length before reaching on any conclusion about disk issue.

Your disk performance is:

  • Excellent, if the value of this counter is less than 75% of total disk capacity.
  • Good, if the value of this counter is up to 85% of total disk capacity.
  • Poor, if the value is constantly more than 85% of total disk capacity.
%Idle Time

%Idle Time measures the percentage of time the disk was idle during the sample interval. The disk is idle when it’s not processing read and write requests. If you are constantly getting its value less than 40% then you can either move some applications from your machine to another machine if you are running other applications as well apart from SQL Server or You can change the current disk system with a faster disk system.

Your disk performance is:

  • Excellent, if the value of this counter is greater than 60% of total disk capacity.
  • Good, if the value of this counter is greater than 50% of total disk capacity.
  • Poor, if the value is constantly less than 40% of total disk capacity.

Related Articles:

I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.

Manvendra Deo Singh
Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:
Summary
Identify Disk Bottleneck in SQL Server using Perfmon Disk Counters
Article Name
Identify Disk Bottleneck in SQL Server using Perfmon Disk Counters
Description
I/O problems are some of the most difficult problems to diagnose and to debug. Here I am going to discuss about perfmon disk counters that will help us in diagnosing the disk related issues.
Author
Publisher Name
www.techyaz.com

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *