sys.dm_io_pending_io_requests DMV (Dynamic Management View), described by BOL as follows: http://msdn.microsoft.com/en-us/library/ms188762.aspx
Returns a row for each pending I/O request in SQL Server.
It’s a very simple DMV used to see all pending I/O requests & there description.
You can combine this DMV with DMF – sys.dm_io_virtual_file_stats to see I/O pending requests with database files. You should run this query multiple times to check if the same files or drive letters consistently coming up on the top. If this is the case that means you facing I/O bottlenecks for that file or drive letter.
Query 1 : Details of I/O pending requests against each DB file
SELECT
DB_NAME(MF.DATABASE_ID) AS [DATABASE],
MF.PHYSICAL_NAME,
IPIR.IO_TYPE,
SUM(IPIR.IO_PENDING) TOTAL_PENDING_IO,
SUM(IPIR.IO_PENDING_MS_TICKS) TOTAL_PENDING_MS_TICKS,
SUM(VFS.NUM_OF_READS) TOTAL_READS,
SUM(VFS.NUM_OF_WRITES) TOTAL_WRITES
FROM
SYS.DM_IO_PENDING_IO_REQUESTS AS IPIR
INNER JOIN
SYS.DM_IO_VIRTUAL_FILE_STATS(NULL,NULL) AS VFS
ON IPIR.IO_HANDLE = VFS.FILE_HANDLE
INNER JOIN
SYS.MASTER_FILES AS MF
ON VFS.DATABASE_ID = VFS.DATABASE_ID
AND VFS.FILE_ID = MF.FILE_ID
GROUP BY MF.DATABASE_ID, MF.PHYSICAL_NAME, IPIR.IO_TYPE
ORDER BY SUM(IPIR.IO_PENDING)
Remarks
1. To use this DMV, User required VIEW SERVER STATE permission on the server.
If you liked this post, do like on Facebook at http://www.facebook.com/mssqlfun
Reference : Rohit Garg (http://mssqlfun.com/)
