Calculating the Percentage of I/O for Each Database using DMV

Below query will display the percentage of I/O for each database on a running SQL 2005/2008 system.

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
)
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
ORDER BY row_num;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s