SQL SERVER – Pending IO request in SQL Server – DMV

Pending IO requests for each database file can be found with one DMV query. I received following question:

“How do we know how many pending IO requests are there for database files (.mdf, .ldf) individually?”

Very interesting question and indeed answer is very interesting as well.

Here is the quick script which I use to find the same. It has to be run in the context of the database for which you want to know pending IO statistics.

USE DATABASE
GO
SELECT vfs.database_id, df.name, df.physical_name
,vfs.FILE_ID, ior.io_pending
FROM sys.dm_io_pending_io_requests ior
INNER JOIN sys.dm_io_virtual_file_stats (DB_ID(), NULL) vfs
ON (vfs.file_handle = ior.io_handle)
INNER JOIN sys.database_files df ON (df.FILE_ID = vfs.FILE_ID)

I keep this script handy as it works like magic every time. If you use any other script please post here and I will post it with due credit.

How to Read Pending IO Results Without Guessing

One thing to know before you use this script: it shows a snapshot of this exact moment. On a healthy server it often returns no rows at all, and that is good news, not a broken query. Run it a few times while the workload is busy, or during a slow period that users complain about, before you draw any conclusions.

Two columns of sys.dm_io_pending_io_requests deserve a closer look. io_pending tells you who is holding the request. A value of 1 means Windows has not finished the I/O yet, which points to the storage side. A value of 0 means Windows has finished it, but SQL Server has not picked up the result yet, which can point to busy schedulers. io_pending_ms_ticks shows how long the request has been waiting, so add it to the column list.

For the bigger picture, query sys.dm_io_virtual_file_stats on its own. Divide io_stall_read_ms by num_of_reads, and io_stall_write_ms by num_of_writes, to get the average wait per read and per write for each file since the last restart. Then compare those numbers with the PAGEIOLATCH_SH and WRITELOG waits in sys.dm_os_wait_stats.

If the same data or log file keeps showing up with requests still waiting on Windows, talk to your storage team with numbers in hand. A list of pending IO requests captured over a few days is much more convincing than saying the disk feels slow. Save each run into a table with a timestamp, so you can line up the results with the slow periods later.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.