Sunday, October 28, 2007

Disk Contention Issues


It is recommended that Average Disk Queue Length for disks be less than 2 * number of spindles on the disk. So if your disk had 10 spindles, then values above 20 woud be a cause of concern. But on a SAN, this particular counter can be misleading at times.

You might want to the check the following counters under perfmon too:

Physical Disk:
Avg. Disk Secs/Write
Avg. Disk Secs/Read
Avg. Disk Secs/Transfer


Logical Disk
Avg. Disk Secs/Write
Avg. Disk Secs/Read
Avg. Disk Secs/Transfer


Also, you would want to find out if there are a lot of Split I/O operations happening on the server.

For disks on which SQL Server Data (MDF,NDF) and Log (LDF) files reside, we recommend that these counters show values less that 30 milli-seconds. If they are constantly showing values of 0.5 and above, then we have a disk contention issue on our hands.

The disk contention could be due to these reasons:

1. There is an underlying H/W issue. This could mean that some driver or firmware needs an upgrade (essentially all your firmware/drivers should always be on the latest builds available) or it could be a problem with the SAN configuration or it could be that this particular SAN is not dedicated to SQL Server.

2. OR it could be that the disk contention is due to the fact that the queries executing on SQL Server are performing excessive amount of reads due to sub-optimal plans. Sub-optimal plans would be generated if your indexes are out of shape or if your statistics are skewed. This would require a statistics update with a full scan. (Statistics maintenance, please refer: http://yukonspace.blogspot.com/2007/10/statistics-update.html)

3. OR it could be that the disk activity on the disks has increased over a period of time due to increase in data or addition of new databases. In such a case, it would be a good idea to provide a separate disk or storage area for some of the databases as the current activity might be too much for the disk to handle. Sometimes, load balancing for physical storage also helps in alleviating a disk contention issue.

Also, it would be a good idea to check if you are receiving any stuck/stalled IO warnings in the SQL Server ERRORLOGs. Please refer http://yukonspace.blogspot.com/2007/09/stuck-stalled-io-messages.html


Related Articles
Improving SQL Server Performance
http://msdn2.microsoft.com/en-us/library/ms998577.aspx

Troubleshooting Performance Problems in SQL Server 2005

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx


Application performance troubleshooting with SQL Server
http://support.microsoft.com/kb/224587

I/O Related DMVs for SQL Server 2005

http://msdn2.microsoft.com/en-us/library/ms190314.aspx

Sometimes, it's also worthwhile checking the activity on tempdb and finding out if the contention is due to tempdb activity. The links below could be helpful:

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

http://support.microsoft.com/kb/328551

No comments: