What Is fn_virtualfilestats() function view in SQL Server?

fn_virtualfilestats() is a Transact-SQL function view that 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.

fn_virtualfilestats() function has the following calling syntax:

fn_virtualfilestats ( { database_id | NULL } , { file_id | NULL } )  

If you call it with NULL arguments, it will return I/O statistics for all files of all databases on this server.

fn_virtualfilestats() returns the following output fields on each data files:

• 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.

Among all output fields, NumberReads, BytesRead, NumberWrites and BytesWritten are very useful to helpful for us to calculate the I/O performance.

For example, the following query returns I/O statistics of the first phisical files in the current database:

select * from fn_virtualfilestats(DB_ID(), 1)

DBID  FILEID  NUMBERREADS  BYTESREAD    NUMBERWRITES  BYTESWRITTEN  ...
6     1  3    114156       41344335872  1077341       39180853248   ...

⇒ Join sys.allocation_units with fn_virtualfilestats()

⇐ Find the File IDs of a Given Table

⇑ SQL Server System Views and Functions

⇑⇑ SQL Server Storage Tutorials