I/O related SQL Server Dynamic Management Views(DMV's)

from http://www.sqlserverspecialists.com/2013/03/io-related-sql-server-dynamic.html

 

This section contains the following dynamic management objects.

 

sys.dm_io_backup_tapes

Returns information about tape devices and the status of mount requests for backups.

 

sys.dm_io_cluster_shared_drives

Similar to fn_servershareddrives in SQL Server 2000, this DMV returns the drive name of each of the shared drives on a clustered server. Only returns data for clustered instances of SQL Server. If the current server instance is not a clustered instance it returns an empty rowset.

 

sys.dm_io_pending_io_requests

Returns one row for each pending I/O request, possibly returning large result sets on very active SQL Servers. 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).

Please visit to know How It Works

You can find one row for each pending IO operation in sys.dm_io_pending_io_requests. In order to get total pending IO operation for database, we have to SUM all the IO operation for each database file we have, following query does the same:

 

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

 

 

sys.dm_io_virtual_file_stats

Similar to fn_virtualfilestats in SQL Server 2000, this DMV function returns I/O statistics for data and log files for one or all databases and one or all files within the database(s).

 

To  find disk I/O performance using sys.dm_io_virtual_file_stats

 

 

Permissions for all I/O related DMV’s :

Requires VIEW SERVER STATE permission on the server.