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()