SQL SERVER – Pending IO request in SQL Server – DMV

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)

SQL SERVER - Pending IO request in SQL Server - DMV iowaitinfile

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.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL DMV
Previous Post
SQLAuthority News – Download – Microsoft SQL Server Compact 4.0
Next Post
SQL SERVER – Guest Post – Architecting Data Warehouse – Niraj Bhatt

Related Posts

4 Comments. Leave new

  • To see the slowest data file, I join the sys.dm_io_virtual_file_stats tabular function with the sys.master_files system view and calculate the average wait time.

    SELECT smf.name AS InternalName
    , smf.physical_name AS PhysicalFile
    , smf.type_desc AS FileType
    , CONVERT(NUMERIC(15,0), ((io_stall_read_ms / num_of_reads)
    + (io_stall_write_ms / num_of_writes)) / 2) AS AverageWait_ms
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) svs
    INNER JOIN sys.master_files smf ON svs.database_id = smf.database_id
    AND svs.file_id = smf.file_id
    ORDER BY AverageWait_ms DESC

    Reply
  • Pinal, I’m using a different query that does outer joins on dm_io_pending_io_requests and dm_io_virtual_file_stats, and I’m finding slowdowns in unknown files – the io_handle doesn’t match up. Any ideas?

    select
    db_name(vfs.database_id) as database_name,
    mf.name as logical_file_name,
    pr.io_type,
    io_handle,
    sum(pr.io_pending_ms_ticks) as sum_io_pending_ms_ticks,
    count(*) as [count]
    from
    sys.dm_io_pending_io_requests pr
    left join sys.dm_io_virtual_file_stats(null, null) vfs on vfs.file_handle = pr.io_handle
    left join sys.master_files mf on mf.database_id = vfs.database_id
    and mf.file_id = vfs.file_id
    where
    pr.io_pending = 1
    group by
    db_name(vfs.database_id),
    mf.name,
    pr.io_type, io_handle

    Reply
  • i like to know , how to find total time taken by io in sql server.i need to find this by query.then what is the difference between elaspsed time and io time.

    Reply

Leave a Reply