sys.dm_io_virtual_file_stats (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Returns I/O statistics for data and log files. This dynamic management function replaces the fn_virtualfilestats function.

Note

To call this from Azure Synapse Analytics, use the name sys.dm_pdw_nodes_io_virtual_file_stats. This syntax is not supported by serverless SQL pool in Azure Synapse Analytics.

Syntax

-- Syntax for SQL Server and Azure SQL Database

sys.dm_io_virtual_file_stats (   
    { database_id | NULL },  
    { file_id | NULL }  
)  

-- Syntax for Azure Synapse Analytics

sys.dm_pdw_nodes_io_virtual_file_stats

Arguments

database_id | NULL

Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database

ID of the database. database_id is int, with no default. Valid inputs are the ID number of a database or NULL. When NULL is specified, all databases in the instance of SQL Server are returned.

The built-in function DB_ID can be specified.

file_id | NULL

Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database

ID of the file. file_id is int, with no default. Valid inputs are the ID number of a file or NULL. When NULL is specified, all files on the database are returned.

The built-in function FILE_IDEX can be specified, and refers to a file in the current database.

Table Returned

Column name	Data type	Description
database_name	sysname	Does not apply to:: SQL Server. Database name. For Azure Synapse Analytics, this is the name of the database stored on the node which is identified by pdw_node_id. Each node has one tempdb database that has 13 files. Each node also has one database per distribution, and each distribution database has 5 files. For example, if each node contains 4 distributions, the results show 20 distribution database files per pdw_node_id.
database_id	smallint	ID of database. In Azure SQL Database, the values are unique within a single database or an elastic pool, but not within a logical server.
file_id	smallint	ID of file.
sample_ms	bigint	Number of milliseconds since the computer was started. This column can be used to compare different outputs from this function. The data type is int for SQL Server 2008 (10.0.x) through SQL Server 2014 (12.x). In these versions, the value will reset to 0 after approximately 25 days of continuous database engine uptime.
num_of_reads	bigint	Number of reads issued on the file.
num_of_bytes_read	bigint	Total number of bytes read on this file.
io_stall_read_ms	bigint	Total time, in milliseconds, that the users waited for reads issued on the file.
num_of_writes	bigint	Number of writes made on this file.
num_of_bytes_written	bigint	Total number of bytes written to the file.
io_stall_write_ms	bigint	Total time, in milliseconds, that users waited for writes to be completed on the file.
io_stall	bigint	Total time, in milliseconds, that users waited for I/O to be completed on the file.
size_on_disk_bytes	bigint	Number of bytes used on the disk for this file. For sparse files, this number is the actual number of bytes on the disk that are used for database snapshots.
file_handle	varbinary	Windows file handle for this file.
io_stall_queued_read_ms	bigint	Does not apply to:: SQL Server 2008 (10.0.x) through SQL Server 2012 (11.x). Total IO latency introduced by IO resource governance for reads. Is not nullable. For more information, see sys.dm_resource_governor_resource_pools (Transact-SQL).
io_stall_queued_write_ms	bigint	Does not apply to:: SQL Server 2008 (10.0.x) through SQL Server 2012 (11.x). Total IO latency introduced by IO resource governance for writes. Is not nullable.
pdw_node_id	int	Applies to: Azure Synapse Analytics Identifier of the node for the distribution.

Remarks

The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started.

Permissions

Requires VIEW SERVER STATE permission. For more information, see Dynamic Management Views and Functions (Transact-SQL).

Permissions for SQL Server 2022 and later

Requires VIEW SERVER PERFORMANCE STATE permission on the server.

Examples

A. Return statistics for a log file

Applies to: SQL Server 2008 (10.0.x) and later, Azure SQL Database

The following example returns statistics for the log file in the AdventureWorks2022 database.

SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks2022'), 2);  
GO  

B. Return statistics for file in tempdb

Applies to: Azure Synapse Analytics

SELECT * FROM sys.dm_pdw_nodes_io_virtual_file_stats 
WHERE database_name = 'tempdb' AND file_id = 2;

See also

Dynamic Management Views and Functions (Transact-SQL)
I O Related Dynamic Management Views and Functions (Transact-SQL)
sys.database_files (Transact-SQL)
sys.master_files (Transact-SQL)