SQL Database IO Consumption

This content 7 years old. Please, read this page keeping its age in mind along with the fact technology changes fast and the information on this page me be outdated, not best practice, or plain wrong.

Here’s a query I found that helps show database IO consumption.

 SELECT
        DB_NAME(DB_ID('DATABASENAME')) AS [Database Name]
      , mf.physical_name
      , io_stall_read_ms
      , num_of_reads
      , CAST(io_stall_read_ms / (1.0 + num_of_reads) AS NUMERIC(10, 1)) AS [avg_read_stall_ms]
      , io_stall_write_ms
      , num_of_writes
      , CAST(io_stall_write_ms / (1.0 + num_of_writes) AS NUMERIC(10, 1)) AS [avg_write_stall_ms]
      , io_stall_read_ms + io_stall_write_ms AS [io_stalls]
      , num_of_reads + num_of_writes AS [total_io]
      , CAST((io_stall_read_ms + io_stall_write_ms)
        / (1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1)) AS [avg_io_stall_ms]
    FROM
        sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
    INNER JOIN sys.master_files AS mf
    ON  fs.database_id = mf.database_id
        AND fs.[file_id] = mf.[file_id]
    ORDER BY
            [Database Name] ASC, mf.physical_name
--  num_of_reads DESC
--  num_of_writes DESC
    OPTION (RECOMPILE) ;

Speak Your Mind

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.