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
Note: Microsoft Books online is a default reference of all articles .