fn_virtualfilestats (Transact-SQL)
Returns I/O statistics for database files, including log files. In SQL Server, this information is also available from the sys.dm_io_virtual_file_stats dynamic management view.
Column Name | Data type | Description |
|---|---|---|
DbId | smallint | Database ID. |
FileId | smallint | File ID. |
TimeStamp | int | Database timestamp at which the data was taken. |
NumberReads | bigint | Number of reads issued on the file. |
BytesRead | bigint | Number of bytes read issued on the file. |
IoStallReadMS | bigint | Total amount of time, in milliseconds, that users waited for the read I/Os to complete on the file. |
NumberWrites | bigint | Number of writes made on the file. |
BytesWritten | bigint | Number of bytes written made on the file. |
IoStallWriteMS | bigint | Total amount of time, in milliseconds, that users waited for the write I/Os to complete on the file. |
IoStallMS | bigint | Sum of IoStallReadMS and IoStallWriteMS. |
FileHandle | bigint | Value of the file handle. |
BytesOnDisk | bigint | Physical file size (count of bytes) on disk. For database files, this is the same value as size in sys.database_files, but is expressed in bytes rather than pages. For database snapshot sparse files, this is the space the operating system is using for the file. |
fn_virtualfilestats is a system table-valued function that gives statistical information, such as the total number of I/Os performed on a file. You can use this function to help keep track of the length of time users have to wait to read or write to a file. The function also helps identify the files that encounter large numbers of I/O activity.
A. Displaying statistical information for a database
The following example displays statistical information for file ID 1 in the database with an ID of 1.
SELECT * FROM fn_virtualfilestats(1, 1); GO
B. Displaying statistical information for a named database and file
The following example displays statistical information for the log file in the AdventureWorks sample database. The system function DB_ID is used to specify the database_id parameter.
SELECT * FROM fn_virtualfilestats(DB_ID(N'AdventureWorks'), 2); GO
C. Displaying statistical information for all databases and files
The following example displays statistical information for all files in all databases in the instance of SQL Server.
SELECT * FROM fn_virtualfilestats(NULL,NULL); GO
