| title | sys.dm_io_virtual_file_stats (Transact-SQL) | Microsoft Docs | ||||
|---|---|---|---|---|---|
| ms.custom | |||||
| ms.date | 03/16/2017 | ||||
| ms.prod | sql-non-specified | ||||
| ms.reviewer | |||||
| ms.suite | |||||
| ms.technology |
|
||||
| ms.tgt_pltfrm | |||||
| ms.topic | language-reference | ||||
| f1_keywords |
|
||||
| dev_langs |
|
||||
| helpviewer_keywords |
|
||||
| ms.assetid | fa3e321f-6fe5-45ff-b397-02a0dd3d6b7d | ||||
| caps.latest.revision | 37 | ||||
| author | JennieHubbard | ||||
| ms.author | jhubbard | ||||
| manager | jhubbard |
[!INCLUDEtsql-appliesto-ss2008-asdb-xxxx-xxx_md]
Returns I/O statistics for data and log files. This dynamic management view replaces the fn_virtualfilestats function.
sys.dm_io_virtual_file_stats (
{ database_id | NULL }
, { file_id | NULL }
)
database_id | NULL
ID of the database. database_id is int, with no default. Valid inputs are the ID number of a database or NULL. When NULL is specified, all databases in the instance of [!INCLUDEssNoVersion] are returned.
The built-in function DB_ID can be specified.
file_id | NULL
ID of the file. file_id is int, with no default. Valid inputs are the ID number of a file or NULL. When NULL is specified, all files on the database are returned.
The built-in function FILE_IDEX can be specified, and refers to a file in the current database.
| Column name | Data type | Description |
|---|---|---|
| database_id | smallint | ID of database. |
| file_id | smallint | ID of file. |
| sample_ms | bigint Applies to: [!INCLUDEssSQL15] through [!INCLUDEssCurrent] int Applies to: [!INCLUDEssKatmai] through [!INCLUDEssSQL14] |
Number of milliseconds since the computer was started. This column 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. |
| io_stall_queued_read_ms | bigint | Applies to: [!INCLUDEssSQL14] through [!INCLUDEssCurrent]. Total IO latency introduced by IO resource governance for reads. Is not nullable. For more information, see sys.dm_resource_governor_resource_pools (Transact-SQL). |
| io_stall_queued_write_ms | bigint | Applies to: [!INCLUDEssSQL14] through [!INCLUDEssCurrent]. Total IO latency introduced by IO resource governance for writes. Is not nullable. |
Requires VIEW SERVER STATE permission. For more information, see Dynamic Management Views and Functions (Transact-SQL).
The following example returns statistics for the log file in the [!INCLUDEssSampleDBnormal] database.
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks2012'), 2);
GO Dynamic Management Views and Functions (Transact-SQL)
I O Related Dynamic Management Views and Functions (Transact-SQL)
sys.database_files (Transact-SQL)
sys.master_files (Transact-SQL)