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:
Thank You!
