2 out of 2 rated this helpful - Rate this topic

sys.dm_io_virtual_file_stats (Transact-SQL)

SQL Server 2012

SQL Server 2005

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

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 AdventureWorks2012 database.

Transact-SQL

SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks2012'), 2);
GO

Reference

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)

Did you find this helpful? Yes No

Not accurate

Not enough depth

Need more code examples

(1500 characters remaining)

Community Additions

ADD