Applies To: SQL Server 2014, SQL Server 2016 Preview
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.
Applies to: SQL Server (SQL Server 2008 through current version).
- 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.
Database timestamp at which the data was taken.
Number of reads issued on the file.
Number of bytes read issued on the file.
Total amount of time, in milliseconds, that users waited for the read I/Os to complete on the file.
Number of writes made on the file.
Number of bytes written made on the file.
Total amount of time, in milliseconds, that users waited for the write I/Os to complete on the file.
Sum of IoStallReadMS and IoStallWriteMS.
Value of the file handle.
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.