SQL Server - How to check Disk Latency

04 November,2012 by Jack Vamvas

Question: I have a SQL Server 2008 that has data , log  and tempdb files placed in separate RAID 10 disks on SAN. In one data drive, which has a single database file (200 GB) there is relatively high avg. disk sec/ read (ranging 100 ms - 900 ms). The SAN system is  optimized for high read rates , according to the storage guys

Do you have any advice on how  reduce the avg. disk sec/read for this drive?.

Answer:  I’m assuming you’re referring to the Physical Disk Counter.  Therefore the avg. disk sec/read stat is captured at the Partition Manager level. In  the Windows Storage Stack , the Partition Manager sits  below the Volume Manager. This means , the delay of 100 ms – 900 ms is the time the IO leaves the Partition Manager and returns.

The challenge is to identify where the delay is occurring below the Partition Manager. Quite often it’s difficult to pinpoint which queue can be creating the delay. For example, there are hardware device driver queues . You may need to speak to your System Administrators to assist in troubleshooting queues

From a DBA perspective , a initial method implement is to monitor the disk queue counters and the DMV - sys.dm_db_index_operational_stats. For a broader performance testing with Perfmon read Performance Monitor Counters for SQL Server performance testing

The sys.dm_db_index_operational_stats  DMV returns  IO , locking , latching and access activity. Useful for identifying index hotspots , waits for read\writes to a table. Will give information about insert, update, and delete. Note: the stats are refreshed after every SQL Server Service restart

Email me at jack@sqlserver-dba.com , for any questions or feedback

       I.[NAME] AS [INDEX NAME], 
         ON I.[OBJECT_ID] = A.[OBJECT_ID] 
            AND I.INDEX_ID = A.INDEX_ID 

Read More

IO_completion on disk solved with Wait Stats and Perfmon - SQL ...

SQL Server - Disk IO performance and SQLIO


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


Verify your Comment

Previewing your Comment

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

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.


Post a comment on SQL Server - How to check Disk Latency

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