Saturday, February 1, 2014

Use sys.dm_io_pending_io_requests MV to check IO contention

use
go

select vfs.database_id,
  df.name,
  df.physical_name,
  vfs.file_id,
  ior.io_pending,
  ior.io_handle,
  ior.io_type
from sys.dm_io_pending_io_requests ior
right outer 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