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.
.gif)
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
|
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.
Requires VIEW SERVER STATE permission on the server.
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
Reference
DB_ID (Transact-SQL)
FILE_IDEX (Transact-SQL)
sys.database_files (Transact-SQL)
sys.master_files (Transact-SQL)
Help and Information
Getting SQL Server 2008 Assistance