What Is fn_virtualfilestats()

Q

What Is fn_virtualfilestats() function view in SQL Server?

✍: FYIcenter.com

A

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

2019-06-29, 149👍, 0💬