Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
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.
Transact-SQL syntax conventions
fn_virtualfilestats ( { database_id | NULL } , { file_id | NULL } )
database_id | NULL
Is the ID of the database. database_id is int, with no default. Specify NULL to return information for all databases in the instance of SQL Server.
file_id | NULL
Is the ID of the file. file_id is int, with no default. Specify NULL to return information for all files in the database.
| Column Name | Data type | Description |
|---|---|---|
| DbId | smallint | Database ID. |
| FileId | smallint | File ID. |
| TimeStamp | bigint | Database timestamp at which the data was taken. int in versions before SQL Server 2016 (13.x). |
| 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.
Requires VIEW SERVER STATE permission on the server.
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
The following example displays statistical information for the log file in the AdventureWorks2022 sample database. The system function DB_ID is used to specify the database_id parameter.
SELECT *
FROM fn_virtualfilestats(DB_ID(N'AdventureWorks2022'), 2);
GO
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
DB_ID (Transact-SQL)
FILE_IDEX (Transact-SQL)
sys.database_files (Transact-SQL)
sys.master_files (Transact-SQL)