Measuring SQL Server File Latency

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.
    • TotalBytes - Total number of bytes reads and writes the file.

  • Read/Write Distribution

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

  • Read Statistics

    • 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_reads AS [NumReads],           --Number of reads issued on the file.
  vfs.num_of_writes AS [NumWrites],         --Number of writes made on this file.
  vfs.num_of_bytes_read AS [ReadBytes],     --Total number of bytes read on this file.
  vfs.num_of_bytes_written AS [WriteBytes], --Total number of bytes written to the file.
  vfs.num_of_bytes_read + vfs.num_of_bytes_written AS [TotatBytes], --Total number of bytes reads and writes the file.

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

--Calculate the average read latency and the average read IO size 
  CASE WHEN vfs.num_of_reads = 0 THEN 0 ELSE   vfs.io_stall_read_ms  / vfs.num_of_reads          END AS [AvgReadLatency_(ms)], 
  CASE WHEN vfs.num_of_reads = 0 THEN 0 ELSE ( vfs.num_of_bytes_read / vfs.num_of_reads ) / 1024 END AS [AvgReadSize_(KB)], 
 
--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 
--  [AvgReadLatency_(ms)]
--  [AvgWriteLatency_(ms)]

Things to Remember Looking at This Data

  • The DMF has data since the start of the instance. The data is reset each time SQL Server is restarted.
  • SQL Server has varying I/O size based on the type of I/O performed. See The post What is SQL Server’s IO Block Size? for more details.
  • When looking at latency take into account that larger I/Os take longer.
  • OLTP systems generally have smaller IOs with lower latency.
  • Data warehouse systems generally have larger IOs with higher latency
  • Transaction log files should always have very low write latency, single-digit milliseconds.
  • Measure performance over time. Looking just at averages can hide peaks in your statistics. If you load a data warehouse once per day, latency might be very high during the load, but the statistics might seem better on average.