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)

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 (http://blog.SQLAuthority.com)

4 thoughts on “SQL SERVER – Pending IO request in SQL Server – DMV

  1. 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

    Like

  2. 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

    Like

  3. 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.

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #020 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s