SQL Server: Cumulative IO By Database since Last Restart

Here’s a TSQL script to get cumulative IO by database since last restart.

WITH Agg_IO_Stats
AS
(
  SELECT
    DB_NAME(database_id) AS database_name,
    CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576 / 1024.
         AS DECIMAL(12, 2)) AS io_in_gb
  FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS DM_IO_Stats
  GROUP BY database_id
),
Rank_IO_Stats
AS
(
  SELECT
    ROW_NUMBER() OVER(ORDER BY io_in_gb DESC) AS row_num,
    database_name,
    io_in_gb,
    CAST(io_in_gb / SUM(io_in_gb) OVER() * 100
         AS DECIMAL(5, 2)) AS pct
  FROM Agg_IO_Stats
)
SELECT R1.row_num, R1.database_name, R1.io_in_gb, R1.pct,
  SUM(R2.pct) AS run_pct
FROM Rank_IO_Stats AS R1
  JOIN Rank_IO_Stats AS R2
    ON R2.row_num <= R1.row_num
GROUP BY R1.row_num, R1.database_name, R1.io_in_gb, R1.pct
ORDER BY R1.row_num;

Source

Posted by Rich Kreider

Father, geek, caffeine addict, IT guy, photographer and after-hours hacker...

Leave a Reply

Your email address will not be published. Required fields are marked *

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