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.

[sourcecode language=”sql”]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[/sourcecode]

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

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

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.