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.
DB_Name(VFS.Database_id) AS DataBaseName
,DF.Physical_Name AS FileName
,SUM(IPR.IO_Pending) AS TotalPendingIO
sys.Dm_io_virtual_file_stats(DB_ID(), NULL) VFS
ON (df.FILE_ID = vfs.FILE_ID)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles .