Get Top N Files With I/O Stall

DROP FUNCTION dbo.udf_GetTopNFilesWithIOStall

GO

CREATE FUNCTION dbo.udf_GetTopNFilesWithIOStall (@n INT)

RETURNS @FilesWithIOStall TABLE

(

IsReadStall INT,

IsWriteStall INT,

IsStall INT,

Drive VARCHAR(2),

DatabaseName SYSNAME,

FileName SYSNAME,

DatabaseFileID INT,

SizeOnDisk_Bytes BIGINT,

NumOfReads BIGINT,

NumOfBytesRead BIGINT,

IOStallRead_MS BIGINT,

NumOfWrites BIGINT,

NumOfBytesWritten BIGINT,

IOStallWrite_MS BIGINT,

IOStall BIGINT

)

AS

/***************************************************************

Purpose: To retrieve the top N file with the greates amount of IO Stalls

(wait for read or write activity)

The results are orderd by the total of read and write stalls, but

generally one of the two values is the high one.

The following DMVs are used:

sys.dm_io_virtual_file_stats - returns IO statistics for all data and log files

sys.master_files - a view that spans the sysfles view in all databases to provide

a list of all DB files in one place.

Author: Jimmy May

History: 24 Feb 2009

****************************************************************/

BEGIN

INSERT @FilesWithIOStall

(

IsReadStall,

IsWriteStall,

IsStall,

Drive,

DatabaseName,

FileName,

DatabaseFileID,

SizeOnDisk_Bytes,

NumOfReads,

NumOfBytesRead,

IOStallRead_MS,

NumOfWrites,

NumOfBytesWritten,

IOStallWrite_MS,

IOStall

)

SELECT TOP (@n)

-- virtual file latency calcs

CASE

WHEN num_of_reads = 0 THEN 0

ELSE (io_stall_read_ms/num_of_reads)

END AS IsReadStall,

CASE

WHEN io_stall_write_ms = 0 THEN 0

ELSE (io_stall_write_ms/num_of_writes)

END AS IsWriteStall,

CASE

WHEN (num_of_reads = 0 AND num_of_writes = 0)THEN 0

ELSE (io_stall/(num_of_reads + num_of_writes))

END AS IsStall,

LEFT(mf.physical_name,2) AS Drive,

DB_NAME(vfs.database_id) AS DatabaseName,

mf.physical_name AS FileName,

vfs.FILE_ID AS DatabaseFileID,

vfs.size_on_disk_bytes AS SizeOnDisk_Bytes,

vfs.num_of_reads AS NumOfReads,

vfs.num_of_bytes_read AS NumOfBytesRead,

vfs.io_stall_read_ms AS IOStallRead_MS,

vfs.num_of_writes AS NumOfWrites,

vfs.num_of_bytes_written AS NumOfBytesWritten,

vfs.io_stall_write_ms AS IOStallWrite_MS,

vfs.io_stall AS IOStall

FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS vfs

JOIN sys.master_files AS mf

ON vfs.database_id = mf.database_id

AND vfs.FILE_ID = mf.FILE_ID

ORDER BY IsStall DESC

RETURN

END