December 6, 2012

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

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

No comments:

Post a Comment

Total Pageviews