- 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 2005SELECT [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 2005SELECT [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 2005SELECT [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 2005SELECT [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