How to Measure IO Latency for Database Files in SQL Server

STAY IN TOUCH

Get New posts delivered straight to your inbox

  • Madeira Team

How to Measure IO Latency for Database Files in SQL Server

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

#DMVs #performancetuning #scripts #io

JOIN OUR MAILING LIST

CONTACT US

 3 Rapaport St. Kfar Saba, Israel

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle