RMJCS Logo
 

sys.dm_io_pending_io_requests

 
 

This dynamic management view returns a row for each pending I/O request in SQL Server - it's a point-in-time snapshot so if there are no pending I/O requests then the view will be empty. Note the io_pending column which indicates whether the I/O request is actually pending (io_pending = 1) or whether SQL Server just hasn't yet dealt with the completed request (io_pending = 0).

Although the io_pending_ms_ticks column is defined in BOL as 'internal use only' it's got a helpful name and according to Microsoft's "Troubleshooting Performance Problems in SQL Server 2005" it represents "... the total time individual I/Os are waiting in the pending queue."

 
     
 

Formatted query for sys.dm_io_pending_io_requests

 
 
select
   db_name(vfs.database_id) as database_name,
   mf.name as logical_file_name,
   pr.io_type,
   --pr.io_completion_request_address,
   --pr.io_pending,
   --pr.io_completion_routine_address,
   --pr.io_user_data_address,
   --pr.scheduler_address,
   --pr.io_handle,
   --pr.io_offset,
   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
 
     
Copyright 2007-2015 RMJCS Ltd