Credits:
Author: ChillyDBA
Date: 27 Jun 2012
Description
Retrieves combined disk read and write stats per database on the server, pus a percentage of total
Code
Function:
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
|
Test Code:
SELECT * FROM dbo.udf_GetIOStats_Summary()
|
 Updating...
Andy Hughes, Jun 27, 2012, 12:20 PM
|