DMV Queries - Get Disk IO Stats (Detail)

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 detailed IO statistics for all database files on the server:
  • Disk Reads
  • Disk Writes
  • Disk Read Stall Time (time spend waiting for disk)
  • Disk Write Stall Time (time spend waiting for disk)
  • Percentage of Total for all 4 of the above figures. 
  •  

Code

Function:

DROP FUNCTION dbo.udf_GetIOStats_Detail
GO

CREATE FUNCTION dbo.udf_GetIOStats_Detail ()
RETURNS @udf_IOStats_Detail TABLE
  
(
      
DatabaseName                SYSNAME,
      
FileType                    VARCHAR(10),
      
DriveLetter                 CHAR(1),
      
BytesRead                   DECIMAL(18,2),
      
BytesWritten                DECIMAL(18,2),
      
IOStallReadTimeSec          DECIMAL(18,2),
      
IOStallWriteTimeSec         DECIMAL(18,2),
      
PercentageReads             DECIMAL(5,2),
      
PercentageWrites            DECIMAL(5,2),
      
PercentageIOStallReadTime   DECIMAL(5,2),
      
PercentageIOStallWriteTime  DECIMAL(5,2)
   )
AS
/***************************************************************
Purpose:   To retrieve a per-file listing of disk reads/write on
           the server since the last restart
          
           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_Detail
  
AS
  
(
      
SELECT
          
DB_NAME(mf.database_id)         AS DatabaseName,
          
          
CASE mf.type_desc
              
WHEN 'Rows' THEN 'DB'
              
ELSE 'LOG'
          
END                             AS FileType,
          
          
mf.physical_name,
          
LEFT(mf.physical_name, 1)        AS DriveLetter,
          
vfs.num_of_writes               AS NumberOfWrites,
          
vfs.num_of_bytes_written        AS BytesWritten,
          
vfs.io_stall_write_ms           AS IOStallWriteMS,
          
mf.type_desc                    AS TypeDesc,
          
vfs.num_of_reads                AS NumberOfReads,
          
vfs.num_of_bytes_read           AS BytesRead,
          
vfs.io_stall_read_ms            AS IOStallReadMS,
          
vfs.io_stall                    AS IOStall,
          
vfs.size_on_disk_bytes          AS SizeOnDiskBytes
      
FROM sys.master_files mf
      
JOIN sys.dm_io_virtual_file_stats(NULL, NULL) vfs
          
ON mf.database_id=vfs.database_id
          
AND mf.FILE_ID=vfs.FILE_ID
  
)
  
  
INSERT @udf_IOStats_Detail
      
(
          
DatabaseName,
          
FileType,
          
DriveLetter,
          
BytesRead,
          
BytesWritten,
          
IOStallReadTimeSec,
          
IOStallWriteTimeSec,
          
PercentageReads,    
          
PercentageWrites,
          
PercentageIOStallReadTime,
          
PercentageIOStallWriteTime
      
)
  
SELECT
      
DatabaseName,
      
FileType,
      
DriveLetter,
      
BytesRead,
      
BytesWritten,
      
IOStallReadMS/1000 AS IOStallReadTimeSec,
      
IOStallWriteMS/1000 AS IOStallWriteTimeSec,
      
PercentageReads = RTRIM(CONVERT(DECIMAL(5,2),BytesRead*100.0/(
                                                                      
SELECT SUM(BytesRead)
                                                                      
FROM IO_Stats_Detail
                                                                  
))),
      
PercentageWrites = RTRIM(CONVERT(DECIMAL(5,2),BytesWritten*100.0/(
                                                                      
SELECT SUM(BytesWritten)
                                                                      
FROM IO_Stats_Detail
                                                                  
))),
      
PercentageIOStallReadTime = RTRIM(CONVERT(DECIMAL(5,2),IOStallReadMS/1000*100.0/(
                                                                      
SELECT SUM(IOStallReadMS/1000)
                                                                      
FROM IO_Stats_Detail
                                                                  
))),
      
PercentageIOStallWriteTime = RTRIM(CONVERT(DECIMAL(5,2),IOStallWriteMS/1000*100.0/(
                                                                      
SELECT SUM(IOStallWriteMS/1000)
                                                                      
FROM IO_Stats_Detail
                                                                  
)))
  
FROM IO_Stats_Detail
  
ORDER BY BytesWritten + BytesRead DESC
  
   RETURN
END

 
 

Test Code:

SELECT * FROM dbo.udf_GetIOStats_Detail()  



Č
ċ
udf_GetIOStats_Detail.sql
(3k)
Andy Hughes,
Jun 27, 2012, 12:17 PM
Comments