Find Pending IO with sys.dm_io_pending_io_requests and sys.Dm_io_virtual_file_stats in SQL Server

Find Pending IO with sys.dm_io_pending_io_requests and sys.Dm_io_virtual_file_stats in SQL Server:

I had one article previously which was providing disk pressure and waits with help of sys.Dm_io_virtual_file_stats DMV. Click here to read that article.

Today I am showing one more use of sys.Dm_io_virtual_file_stats DMV along with sys.dm_io_pending_io_requests to find pending IO operation in your database. Curiosity is one of the good qualities of every DBA. They should have knowledge of exactly what is going on the SQL Server, what is the status of IO, how many IO operations are waiting for its turn and much more.

You can find one row for each pending IO operation in sys.dm_io_pending_io_requests. In order to get total pending IO operation for database, we have to SUM all the IO operation for each database file we have, following query does the same.

SELECT

DB_Name(VFS.Database_id) AS DataBaseName

,DF.Physical_Name AS FileName

,SUM(IPR.IO_Pending) AS TotalPendingIO

FROM

sys.dm_io_pending_io_requests IPR

INNER JOIN

sys.Dm_io_virtual_file_stats(DB_ID(), NULL) VFS

ON

IPR.IO_Handle=VFS.File_Handle

INNER JOIN

sys.database_files df

ON (df.FILE_ID = vfs.FILE_ID)

GROUP BY

VFS.Database_id,DF.Physical_Name

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles .