Get Disk IO Stats (Summary)

DROP FUNCTION dbo.udf_GetIOStats_Summary

GO

CREATE FUNCTION dbo.udf_GetIOStats_Summary ()

RETURNS @udf_IOStats_Summary TABLE

(

RowNUM INT,

DatabaseName SYSNAME,

IO_IN_MB DECIMAL(18,2),

Pct DECIMAL(5,2)

)

AS

/***************************************************************

Purpose: To retrieve a per-database summary of IO on the server

since the last restart

IO is the total of the bytes read and written

The following DMVs are used:

sys.dm_io_virtual_file_stats - contains detailed IO information on a per-file basis

Author: ChillyDBA

History: 27 Jun 2012 - Initial Issue

****************************************************************/

BEGIN

;WITH IO_Stats_Summary

AS

(

SELECT

DB_NAME(database_id) AS DatabaseName,

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

)

INSERT @udf_IOStats_Summary

(

RowNUM,

DatabaseName,

IO_IN_MB,

Pct

)

SELECT

ROW_NUMBER() OVER(ORDER BY io_in_mb DESC) AS RowNum,

DatabaseName,

IO_in_MB,

CAST(IO_in_MB / SUM(IO_in_MB) OVER() * 100

AS DECIMAL(5, 2)) AS Pct

FROM IO_Stats_Summary

ORDER BY RowNum;

RETURN

END

SELECT * FROM dbo.udf_GetIOStats_Summary()