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
|