Get Disk IO Stats (Detail)

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

SELECT * FROM dbo.udf_GetIOStats_Detail()