Script to find Read Write Latency in SQL Server

Script to find Read Write Latency in SQL Server

In order to view the disk latencies in SQL Server we can quickly and easily query the DMV sys.dm_io_virtual_file_stats. This DMV accepts two parameters: database_id and file_id.

You can also pass NULL as both values and return the latencies for all files for all databases. The output columns include:

database_id

file_id

sample_ms

num_of_reads

num_of_bytes_read

io_stall_read_ms

num_of_writes

num_of_bytes_written

io_stall_write_ms

io_stall

size_on_disk_bytes

file_handle

What values are good or bad?

If you check Paul Randal blog on SQLskills, You will find below values to make your decision about good or bad latencies:

Excellent: < 1ms

Very good: < 5ms

Good: 5 – 10ms

Poor: 10 – 20ms

Bad: 20 – 100ms

Really bad: 100 – 500ms

OMG!: > 500ms

Below is the SQL script which I generally used to check the disk latencies. This script is created by Paul Randal. It allows you to filter on read or write latencies and it will also provide you the database names and file paths.

SQL Script:

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]

-- WHERE [vfs].[file_id] = 2 -- log files

-- ORDER BY [Latency] DESC

-- ORDER BY [ReadLatency] DESC

ORDER BY [WriteLatency] DESC;

GO

You can check the detailed info here:

How to examine IO subsystem latencies from within SQL Server

Thank You!