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 firstname.lastname@example.org , 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