DMV Queries - Get Disk IO Stats (Summary)

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

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



ċ
udf_GetIOStats_Summary.sql
(1k)
Andy Hughes,
Jun 27, 2012, 12:20 PM
Comments