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, typicallyROWS
orLOG
.
-
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 filePercentBytesWrite
- 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.