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. 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.
|
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.
|
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 AdventureWorks database.
SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks'), 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)
Help and Information
Getting SQL Server 2008 Assistance