A DMV a Day – Day 28

The DMV for Day 28 is sys.dm_io_pending_io_requests, which is described by BOL as:

Returns a row for each pending I/O request in SQL Server.

That is a pretty straightforward, if terse description.  This DMV works with SQL Server 2005, SQL Server 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

-- Look at pending I/O requests by file
SELECT DB_NAME(mf.database_id) AS [Database], mf.physical_name, 
r.io_pending, r.io_pending_ms_ticks, r.io_type, fs.num_of_reads, fs.num_of_writes
FROM sys.dm_io_pending_io_requests AS r
INNER JOIN sys.dm_io_virtual_file_stats(null,null) AS fs
ON r.io_handle = fs.file_handle 
INNER JOIN sys.master_files AS mf
ON fs.database_id = mf.database_id
AND fs.file_id = mf.file_id
ORDER BY r.io_pending, r.io_pending_ms_ticks DESC; 

You would want to run this query multiple times to see if the same files (and drive letters) consistently show up at the top of the list. If that happens, that would be evidence that you were seeing I/O bottlenecks for that file or drive letter. You could use this to help convince your SAN engineer that your were seeing I/O issues for a particular LUN.

The last two columns in the query show the cumulative number of read and writes for the file since SQL Server was started (or since the file was created, whichever was shorter). Knowing that information would be helpful if you were trying to decide which RAID level to use for a particular drive letter.