Listing 4: Calculating a Running Percentage on the Filtered Databases

WITH Agg_IO_Stats
AS
(
  SELECT
    DB_NAME(database_id) AS database_name,
    CAST(SUM(num_of_bytes_read + num_of_bytes_written) / 1048576.
         AS DECIMAL(12, 2)) AS io_in_mb
  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_mb DESC) AS row_num,
    database_name,
    io_in_mb,
    CAST(io_in_mb / SUM(io_in_mb) OVER() * 100
         AS DECIMAL(5, 2)) AS pct
  FROM Agg_IO_Stats
)
SELECT R1.row_num, R1.database_name, R1.io_in_mb, 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_mb, R1.pct
HAVING SUM(R2.pct) - R1.pct < 90 -- pct threshold
    OR R1.row_num <= 3           -- min rows to return
ORDER BY R1.row_num;





1