Monitor Read/Write Latency on your SQL Database

SQL query to monitor Read/Write latency on your SQL Server database. Can be of great help while performance tuning your SQL database.

Copy and run this query against your database:

SELECT
    [ReadLatencyAvg ms] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatencyAvg ms] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    [LatencyAvg ms] =
        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],
[vfs].[file_id]
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]
-- WHERE [vfs].[file_id] = 1 -- mdf data files
-- WHERE [vfs].[file_id] = 2 -- log files
-- ORDER BY [LatencyAvg ms] DESC
-- ORDER BY [ReadLatencyAvg ms] DESC
ORDER BY [WriteLatencyAvg ms] DESC;
GO

Industry-wide bechmarks for performance evaluation:
-- Excellent: < 1ms
-- Very good: < 5ms
-- Good: 5 – 10ms
-- Poor: 10 – 20ms
-- Bad: 20 – 100ms
-- Really bad: 100 – 500ms
-- Really Really Bad: > 500ms