sys.dm_io_virtual_file_stats (Transact-SQL)
Returns I/O statistics for data and log files. This dynamic management view replaces the fn_virtualfilestats function.
Syntax
sys.dm_io_virtual_file_stats (
{ database_id | NULL }
, { file_id | NULL }
)
Arguments
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 SQL Server are returned.The built-in function DB_ID can be specified. When using DB_ID without specifying a database name, the compatibility level of the current database must be 90.
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.
Table Returned
Column name |
Data 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 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. |
Permissions
Requires VIEW SERVER STATE permission. For more information, see Dynamic Management Views and Functions (Transact-SQL).
Examples
The following example returns statistics for the log file in the AdventureWorks2008R2 database.
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks2008R2'), 2);
GO