sys.dm_io_virtual_file_stats (Transact-SQL)

 

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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 SQL Server 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 nameData typeDescription
database_idsmallintID of database.
file_idsmallintID of file.
sample_msbigint

Applies to: SQL Server 2016 through SQL Server 2016

 int

Applies to: SQL Server 2008 through SQL Server 2014
Number of milliseconds since the computer was started. This column can be used to compare different outputs from this function.
num_of_readsbigintNumber of reads issued on the file.
num_of_bytes_readbigintTotal number of bytes read on this file.
io_stall_read_msbigintTotal time, in milliseconds, that the users waited for reads issued on the file.
num_of_writesbigintNumber of writes made on this file.
num_of_bytes_writtenbigintTotal number of bytes written to the file.
io_stall_write_msbigintTotal time, in milliseconds, that users waited for writes to be completed on the file.
io_stallbigintTotal time, in milliseconds, that users waited for I/O to be completed on the file.
size_on_disk_bytesbigintNumber 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_handlevarbinaryWindows file handle for this file.
io_stall_queued_read_msbigintApplies to: SQL Server 2014 through SQL Server 2016.

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_msbigintApplies to: SQL Server 2014 through SQL Server 2016.

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 AdventureWorks2012 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)

Community Additions

ADD
Show: