I/O Related Dynamic Management Views and Functions

Se more view version maps here: Microsoft Sql System View Version Maps

• sys.dm_io_backup_tapes
• sys.dm_io_cluster_shared_drives
• sys.dm_io_pending_io_requests
• sys.dm_io_virtual_file_stats

sys.dm_io_backup_tapes

pending I/O request in SQL Server.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
io_completion_request_address	•	•	•	•	varbinary(8)	Memory address of the IO request. Is not nullable.
io_type	•	•	•	•	varchar(7)	Type of pending I/O request. Is not nullable.
io_pending	•	•	•	•	int	Indicates whether the I/O request is pending or has been completed by Windows. An I/O request can still be pending even when Windows has completed the request, but SQL Server has not yet performed a context switch in which it would process the I/O request and remove it from this list. Is not nullable.
io_completion_routine_address	•	•	•	•	varbinary(8)	Internal function to call when the I/O request is completed. Is nullable.
io_user_data_address	•	•	•	•	varbinary(8)	Internal use only. Is nullable.
scheduler_address	•	•	•	•	varbinary(8)	Scheduler on which this I/O request was issued. The I/O request will appear on the pending I/O list of the scheduler. For more information, see sys.dm_os_schedulers1. Is not nullable.
io_handle	•	•	•	•	varbinary(8)	File handle of the file that is used in the I/O request. Is nullable.
io_offset	•	•	•	•	bigint	Offset of the I/O request. Is not nullable.
io_pending_ms_ticks	•	•	•	•	int	Internal use only. Is not nullable.

TSQL

Sql 2005
SELECT [io_completion_request_address], [io_type], [io_pending], [io_completion_routine_address], [io_user_data_address], [scheduler_address], [io_handle], [io_offset], [io_pending_ms_ticks] FROM sys.dm_io_backup_tapes
Sql 2008
SELECT [io_completion_request_address], [io_type], [io_pending], [io_completion_routine_address], [io_user_data_address], [scheduler_address], [io_handle], [io_offset], [io_pending_ms_ticks] FROM sys.dm_io_backup_tapes
Sql 2008 R2
SELECT [io_completion_request_address], [io_type], [io_pending], [io_completion_routine_address], [io_user_data_address], [scheduler_address], [io_handle], [io_offset], [io_pending_ms_ticks] FROM sys.dm_io_backup_tapes
Sql 2012
SELECT [io_completion_request_address], [io_type], [io_pending], [io_completion_routine_address], [io_user_data_address], [scheduler_address], [io_handle], [io_offset], [io_pending_ms_ticks] FROM sys.dm_io_backup_tapes

Back to Top

---

sys.dm_io_cluster_shared_drives

This view returns the drive name of each of the shared drives if the current server instance is a clustered server. If the current server instance is not a clustered instance it returns an empty rowset.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
drivename	•	•	•	•	nchar(2)	The name of the drive (the drive letter) that represents an individual disk taking part in the cluster shared disk array. Column is not nullable.

TSQL

Sql 2005
SELECT [drivename] FROM sys.dm_io_cluster_shared_drives
Sql 2008
SELECT [drivename] FROM sys.dm_io_cluster_shared_drives
Sql 2008 R2
SELECT [drivename] FROM sys.dm_io_cluster_shared_drives
Sql 2012
SELECT [drivename] FROM sys.dm_io_cluster_shared_drives

Back to Top

---

sys.dm_io_pending_io_requests

pending I/O request in SQL Server.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
io_completion_request_address	•	•	•	•	varbinary(8)	Memory address of the IO request. Is not nullable.
io_type	•	•	•	•	varchar(7)	Type of pending I/O request. Is not nullable.
io_pending	•	•	•	•	int	Indicates whether the I/O request is pending or has been completed by Windows. An I/O request can still be pending even when Windows has completed the request, but SQL Server has not yet performed a context switch in which it would process the I/O request and remove it from this list. Is not nullable.
io_completion_routine_address	•	•	•	•	varbinary(8)	Internal function to call when the I/O request is completed. Is nullable.
io_user_data_address	•	•	•	•	varbinary(8)	Internal use only. Is nullable.
scheduler_address	•	•	•	•	varbinary(8)	Scheduler on which this I/O request was issued. The I/O request will appear on the pending I/O list of the scheduler. For more information, see sys.dm_os_schedulers1. Is not nullable.
io_handle	•	•	•	•	varbinary(8)	File handle of the file that is used in the I/O request. Is nullable.
io_offset	•	•	•	•	bigint	Offset of the I/O request. Is not nullable.
io_pending_ms_ticks	•	•	•	•	int	Internal use only. Is not nullable.

TSQL

Sql 2005
SELECT [io_completion_request_address], [io_type], [io_pending], [io_completion_routine_address], [io_user_data_address], [scheduler_address], [io_handle], [io_offset], [io_pending_ms_ticks] FROM sys.dm_io_pending_io_requests
Sql 2008
SELECT [io_completion_request_address], [io_type], [io_pending], [io_completion_routine_address], [io_user_data_address], [scheduler_address], [io_handle], [io_offset], [io_pending_ms_ticks] FROM sys.dm_io_pending_io_requests
Sql 2008 R2
SELECT [io_completion_request_address], [io_type], [io_pending], [io_completion_routine_address], [io_user_data_address], [scheduler_address], [io_handle], [io_offset], [io_pending_ms_ticks] FROM sys.dm_io_pending_io_requests
Sql 2012
SELECT [io_completion_request_address], [io_type], [io_pending], [io_completion_routine_address], [io_user_data_address], [scheduler_address], [io_handle], [io_offset], [io_pending_ms_ticks] FROM sys.dm_io_pending_io_requests

Back to Top

---

sys.dm_io_virtual_file_stats

Returns I/O statistics for data and log files. This dynamic management view replaces the fn_virtualfilestats1 function.

Column name	Sql 2005	Sql 2008	Sql 2008 R2	Sql 2012	Type	Description
database_id	•	•	•	•	smallint	ID of database.
file_id	•	•	•	•	smallint	ID of file.
sample_ms	•	•	•	•	int	Number of milliseconds since the computer was started. This column is can be used to compare different outputs from this function.
num_of_reads	•	•	•	•	bigint	Number of reads issued on the file.
num_of_bytes_read	•	•	•	•	bigint	Total number of bytes read on this file.
io_stall_read_ms	•	•	•	•	bigint	Total time, in milliseconds, that the users waited for reads issued on the file.
num_of_writes	•	•	•	•	bigint	Number of writes made on this file.
num_of_bytes_written	•	•	•	•	bigint	Total number of bytes written to the file.
io_stall_write_ms	•	•	•	•	bigint	Total time, in milliseconds, that users waited for writes to be completed on the file.
io_stall	•	•	•	•	bigint	Total time, in milliseconds, that users waited for I/O to be completed on the file.
size_on_disk_bytes	•	•	•	•	bigint	Number of bytes used on the disk for this file. For sparse files, this number is the actual number of bytes on the disk that are used for database snapshots.
file_handle	•	•	•	•	varbinary	Windows file handle for this file.

TSQL

Sql 2005
SELECT [database_id], [file_id], [sample_ms], [num_of_reads], [num_of_bytes_read], [io_stall_read_ms], [num_of_writes], [num_of_bytes_written], [io_stall_write_ms], [io_stall], [size_on_disk_bytes], [file_handle] FROM sys.dm_io_virtual_file_stats
Sql 2008
SELECT [database_id], [file_id], [sample_ms], [num_of_reads], [num_of_bytes_read], [io_stall_read_ms], [num_of_writes], [num_of_bytes_written], [io_stall_write_ms], [io_stall], [size_on_disk_bytes], [file_handle] FROM sys.dm_io_virtual_file_stats
Sql 2008 R2
SELECT [database_id], [file_id], [sample_ms], [num_of_reads], [num_of_bytes_read], [io_stall_read_ms], [num_of_writes], [num_of_bytes_written], [io_stall_write_ms], [io_stall], [size_on_disk_bytes], [file_handle] FROM sys.dm_io_virtual_file_stats
Sql 2012
SELECT [database_id], [file_id], [sample_ms], [num_of_reads], [num_of_bytes_read], [io_stall_read_ms], [num_of_writes], [num_of_bytes_written], [io_stall_write_ms], [io_stall], [size_on_disk_bytes], [file_handle] FROM sys.dm_io_virtual_file_stats

Back to Top

---