Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
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

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       A.LEAF_INSERT_COUNT, 
       A.LEAF_UPDATE_COUNT, 
       A.LEAF_DELETE_COUNT 
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A 
       INNER JOIN SYS.INDEXES AS I 
         ON I.[OBJECT_ID] = A.[OBJECT_ID] 
            AND I.INDEX_ID = A.INDEX_ID 
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

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)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

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


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