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