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

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