Monthly Archives: October 2013

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 = MF.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/)