Post reply

Getting more info out of dm_io_pending_io_requests

  • March 3, 2017 at 1:40 pm

    #322275

    Hi there. 
    From time, the SQL Server I/O activity for a database we host starts increasing, up to the point that we start experiencing slowness, and associated issues. We are troubleshooting this in different ways. 

    Currently I'm interested in knowing if it's possible to connect the information from this DMV sys.dm_io_pending_io_requests with a specific query, or gather some more specific information out of it. 

    I mean, during the spikes, we will see many PENDING on DISK results http://imgur.com/a/NjRTj

    Is it possible to get more concrete information out of it ? It says you have pending IO on disk, but this info alone is not enough.
    E.g. want to know what's going on, what is the query or task triggering this increase.

    I can easily reproduce this, e.g. if I rebuild a large index, the PENDING on DISK will go out of scale, but is there a way, by looking at those stats, to connect it back with whatever is causing it ? In my example an index rebuild command.

    Cordially, Agustin 🙂

  • March 3, 2017 at 2:30 pm

    #1931635

    I found that sys.dm_exec_requests has a Schedule_ID field in it.  You can join dm_io_pending_io_requests to it on that field.  Join to Exec_Requests and exec_sql_text and you can get what you are after.  the first query I got from Pinal Dave: Here

    Second query I came up with after looking at BOL.

    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);
    GO

    SELECT    ipir.io_type, ipir.io_pending,
        ipir.scheduler_address, ipir.io_handle,
        os.scheduler_id, os.cpu_id, os.pending_disk_io_count,
        ER.session_id, ER.command, ER.cpu_time, ST.text

    FROM sys.dm_io_pending_io_requests ipir
    INNER JOIN sys.dm_os_schedulers os ON ipir.scheduler_address = os.scheduler_address
    INNER JOIN sys.dm_exec_requests AS ER ON os.scheduler_id = ER.Scheduler_id
    CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST

    Also, I'm not proficient with Extended Events, but I am wondering if you can create an XE session to get this information for you programmatically when the pending disk events occur.

  • March 7, 2017 at 5:49 am

    #1932017

    Thanks a lot for your reply 🙂
    I've been testing it, and it's working as expected 🙂 When there is pending IO, this query will get you information about it.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply