TSQL Latency and I/O Statistics Queries
1. Average Latency
To calculate the average read and write latency, you can use the following SQL query. It breaks down the latency by reads, writes, and transfers:
-- Calculate Avg Latency
SELECT
[ReadLatency] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
[AvgBPerRead] = CASE WHEN [num_of_reads] = 0 THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] = CASE WHEN [num_of_writes] = 0 THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] = CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0) THEN 0 ELSE
(([num_of_bytes_read] + [num_of_bytes_written]) / ([num_of_reads] + [num_of_writes])) END,
LEFT([mf].[physical_name], 2) AS [Drive],
DB_NAME([vfs].[database_id]) AS [DB],
[mf].[physical_name]
FROM
sys.dm_io_virtual_file_stats(NULL, NULL) AS [vfs]
JOIN
sys.master_files AS [mf] ON [vfs].[database_id] = [mf].[database_id] AND [vfs].[file_id] = [mf].[file_id]
ORDER BY
[WriteLatency] DESC;
2. I/O Statistics by File for Current Database
This query helps you track I/O statistics by file for the current database, including read and write percentages and I/O stall breakdowns.
-- Check I/O Statistics by file for the current database
SELECT
DB_NAME(DB_ID()) AS [DB_Name],
DFS.name AS [Logical_Name],
DIVFS.[file_id],
DFS.physical_name AS [PH_Name],
DIVFS.num_of_reads,
DIVFS.num_of_writes,
DIVFS.io_stall_read_ms,
DIVFS.io_stall_write_ms,
CAST(100. * DIVFS.io_stall_read_ms / (DIVFS.io_stall_read_ms + DIVFS.io_stall_write_ms) AS DECIMAL(10, 1)) AS [IO_Stall_Reads_Pct],
CAST(100. * DIVFS.io_stall_write_ms / (DIVFS.io_stall_write_ms + DIVFS.io_stall_read_ms) AS DECIMAL(10, 1)) AS [IO_Stall_Writes_Pct],
(DIVFS.num_of_reads + DIVFS.num_of_writes) AS [Writes + Reads],
CAST(DIVFS.num_of_bytes_read / 1048576.0 AS DECIMAL(10, 2)) AS [MB Read],
CAST(DIVFS.num_of_bytes_written / 1048576.0 AS DECIMAL(10, 2)) AS [MB Written],
CAST(100. * DIVFS.num_of_reads / (DIVFS.num_of_reads + DIVFS.num_of_writes) AS DECIMAL(10, 1)) AS [# Reads Pct],
CAST(100. * DIVFS.num_of_writes / (DIVFS.num_of_reads + DIVFS.num_of_writes) AS DECIMAL(10, 1)) AS [# Write Pct],
CAST(100. * DIVFS.num_of_bytes_read / (DIVFS.num_of_bytes_read + DIVFS.num_of_bytes_written) AS DECIMAL(10, 1)) AS [Read Bytes Pct],
CAST(100. * DIVFS.num_of_bytes_written / (DIVFS.num_of_bytes_read + DIVFS.num_of_bytes_written) AS DECIMAL(10, 1)) AS [Written Bytes Pct]
FROM
sys.dm_io_virtual_file_stats(DB_ID(), NULL) AS DIVFS
INNER JOIN
sys.database_files AS DFS WITH (NOLOCK) ON DIVFS.[file_id] = DFS.[file_id];
3. I/O Utilization by Database
The following query shows the I/O statistics for each database in your system, helping you determine the total I/O in megabytes and its percentage of the system's total I/O.
-- Check I/O utilization by database
WITH AggregateIOStatistics AS
(
SELECT
DB_NAME(database_id) AS [DB Name],
CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576 AS DECIMAL(12, 2)) AS io_in_mb
FROM
sys.dm_io_virtual_file_stats(NULL, NULL) AS [DM_IO_STATS]
GROUP BY
database_id
)
SELECT
ROW_NUMBER() OVER (ORDER BY io_in_mb DESC) AS [I/O Rank],
[DB Name],
io_in_mb AS [Total I/O (MB)],
CAST(io_in_mb / SUM(io_in_mb) OVER() * 100.0 AS DECIMAL(5, 2)) AS [I/O Percent]
FROM
AggregateIOStatistics
ORDER BY
[I/O Rank];
4. Drive Level Latency Analysis
Use this query to check the latency per disk drive. The levels of latency are categorized as follows:
- Brilliant: < 1ms
- Great: < 5ms
- Good Quality: 5 – 10ms
- Poor: 10 – 20ms
- Horrific: 20 – 100ms
- Disgracefully Bad: 100 – 500ms
- WOW!: > 500ms
-- Check Drive level latency
SELECT
DISK_Drive,
CASE WHEN DISK_num_of_reads = 0 THEN 0 ELSE (DISK_io_stall_read_ms / DISK_num_of_reads) END AS [Read Latency],
CASE WHEN DISK_io_stall_write_ms = 0 THEN 0 ELSE (DISK_io_stall_write_ms / DISK_num_of_writes) END AS [Write Latency],
CASE WHEN (DISK_num_of_reads = 0 AND DISK_num_of_writes = 0) THEN 0 ELSE (DISK_io_stall / (DISK_num_of_reads + DISK_num_of_writes)) END AS [Overall Latency],
CASE WHEN DISK_num_of_reads = 0 THEN 0 ELSE (DISK_num_of_bytes_read / DISK_num_of_reads) END AS [Avg Bytes/Read],
CASE WHEN DISK_io_stall_write_ms = 0 THEN 0 ELSE (DISK_num_of_bytes_written / DISK_num_of_writes) END AS [Avg Bytes/Write],
CASE WHEN (DISK_num_of_reads = 0 AND DISK_num_of_writes = 0) THEN 0 ELSE ((DISK_num_of_bytes_read + DISK_num_of_bytes_written) / (DISK_num_of_reads + DISK_num_of_writes)) END AS [Avg Bytes/Transfer]
FROM
(SELECT
LEFT(UPPER(mf.physical_name), 2) AS DISK_Drive,
SUM(num_of_reads) AS DISK_num_of_reads,
SUM(io_stall_read_ms) AS DISK_io_st
