# Measuring SQL Server File Latency

Page content

This post is a reference post for retrieving IO statistics for data and log files in SQL Server. We’ll look at where we can find IO statistics in SQL Server, query it to produce meaningful metrics, and discuss some key points when interpreting this data.

## The Source DMF

The primary source for file latency data is the dynamic management function sys.dm_io_virtual_file_stats. The data in this DMF is per file. The query below joins with sys.master_files.

## Key Data Points Captured

Let’s walk through the columns returned and their meaning.

• Database information

• DBName - The name of the database associated with this file.
• FileName - The logical name of the file for this physical file.
• FileType - The type of file for this file, typically ROWS or LOG.
• Raw IO Data

• NumReads - The number of reads issued on this file.
• NumWrites - The number of writes issued on this file.
• ReadBytes - Total number of bytes read on this file.
• WriteBytes - Total number of bytes written to this file.

• PercentBytesRead - The percent reads on this file
• PercentBytesWrite - the percent writes on this file.

• AvgReadLatency_(ms) - The average read latency in milliseconds (ms) on this file.
• AvgReadSize_(KB) - The average read IO size in kilobytes (KB) on this file.
• Write Statistics

• AvgWriteLatency_(ms) - The average write latency in milliseconds (ms) on this file.
• AvgWriteSize_(KB) - The average read IO size in kilobytes (KB) on this file.
• Total Statistics for all IOs

• AvgLatency_(ms) - The averate latency, read and write, in milliseconds (ms) on this file.
• AvgIOSize_(KB) - The average IO size, read and write, in kilobytes (KB) on this file.
• Physical File

• PhysicalFileName - The physical file name.

## The Query

SELECT
DB_NAME(mf.database_id) AS [DBName],
mf.name AS [FileName],
mf.type_desc AS [FileType],
vfs.num_of_writes AS [NumWrites],         --Number of writes made on this file.
vfs.num_of_bytes_written AS [WriteBytes], --Total number of bytes written to the file.

--Calculate the percentage of bytes read or written to the file
vfs.num_of_bytes_written * 100 / (( vfs.num_of_bytes_read + vfs.num_of_bytes_written ))  AS [PercentBytesWrite],

--Calculate the average write latency and the average write IO size
CASE WHEN vfs.num_of_writes = 0 THEN 0 ELSE   vfs.io_stall_write_ms    / vfs.num_of_writes          END AS [AvgWriteLatency_(ms)],
CASE WHEN vfs.num_of_writes = 0 THEN 0 ELSE ( vfs.num_of_bytes_written / vfs.num_of_writes ) / 1024 END AS [AvgWriteSize_(KB)],

--Calculate the average total latency and the average IO size
CASE WHEN vfs.num_of_reads + vfs.num_of_writes = 0 THEN 0 ELSE vfs.io_stall / ( vfs.num_of_reads + vfs.num_of_writes ) END AS [AvgLatency_(ms)],
CASE WHEN vfs.num_of_reads + vfs.num_of_writes = 0 THEN 0
ELSE ( vfs.num_of_bytes_read + vfs.num_of_bytes_written ) / ( vfs.num_of_reads + vfs.num_of_writes ) / 1024 END AS [AvgIOSize_(KB)],

--The physical file name
mf.physical_name AS [PhysicalFileName]

FROM
sys.dm_io_virtual_file_stats(NULL, NULL) as [vfs]
inner join sys.master_files as [mf] ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
ORDER BY
[AvgLatency_(ms)] DESC