In order to get information about the amounts of PHYSICAL reads we perform on database files, SQL Server exposes a DMV called sys.dm_io_virtual_file_stats.
In addition to information about number of reads and writes, and the amounts of bytes read and written, we also have the io_stall_read_ms, io_stall_write_ms and io_stall, which show information about the amount of time we have to wait when reading from each file.
Using the DMV columns, the following script takes a snapshot of the DMV, sleeps for 1 minute and then compares the current state to the state of the snapshot, in order to return the latency per read and per write.
How to Measure IO Latency for Database Files
Transact-SQL
/*======================================================================================================================== Description: This script measures the IO latency for reads and writes for every database file in the instance The script captures a snapshot of sys.dm_io_virtual_file_stats, sleeps for 1 minute and compares the current state against the snapshot Author: Matan Yungman, http://www.madeirasql.com/how-to-measure-io-latency-for-database-files =========================================================================================================================*/ IF OBJECT_ID('tempdb..#io') is not null DROP TABLE #io GO SELECT * INTO #io FROM sys.dm_io_virtual_file_stats(null,null) WAITFOR DELAY '00:01:00' SELECT DB_NAME(a.database_id), a.file_id, a.num_of_reads-b.num_of_reads AS num_of_reads, a.num_of_writes-b.num_of_writes as num_of_writes, CASE WHEN a.num_of_reads-b.num_of_reads > 0 THEN (a.io_stall_read_ms-b.io_stall_read_ms)/(a.num_of_reads-b.num_of_reads) ELSE 0 END AS read_latency, CASE WHEN a.num_of_writes-b.num_of_writes > 0 THEN (a.io_stall_write_ms-b.io_stall_write_ms)/(a.num_of_writes-b.num_of_writes) ELSE 0 END AS write_latency FROM #io b inner join sys.dm_io_virtual_file_stats(null,null) a ON a.database_id = b.database_id and a.file_id = b.file_id ORDER BY DB_NAME(a.database_id) GO
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
/*========================================================================================================================
Description: This script measures the IO latency for reads and writes for every database file in the instance
The script captures a snapshot of sys.dm_io_virtual_file_stats, sleeps for 1 minute and compares
the current state against the snapshot
Author: Matan Yungman, http://www.madeirasql.com/how-to-measure-io-latency-for-database-files
=========================================================================================================================*/
IF OBJECT_ID('tempdb..#io') is not null
DROP TABLE #io
GO
SELECT * INTO #io FROM sys.dm_io_virtual_file_stats(null,null)
WAITFOR DELAY '00:01:00'
SELECT
DB_NAME(a.database_id),
a.file_id,
a.num_of_reads-b.num_of_reads AS num_of_reads,
a.num_of_writes-b.num_of_writes as num_of_writes,
CASE
WHEN a.num_of_reads-b.num_of_reads > 0
THEN
(a.io_stall_read_ms-b.io_stall_read_ms)/(a.num_of_reads-b.num_of_reads)
ELSE 0
END AS read_latency,
CASE
WHEN
a.num_of_writes-b.num_of_writes > 0
THEN
(a.io_stall_write_ms-b.io_stall_write_ms)/(a.num_of_writes-b.num_of_writes)
ELSE 0
END AS write_latency
FROM #io b inner join sys.dm_io_virtual_file_stats(null,null) a
ON a.database_id = b.database_id and a.file_id = b.file_id
ORDER BY DB_NAME(a.database_id)
GO