Get Top N Files With I/O Stall

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

Date: 16 May 2009

Description

To retrieve the top N file with the greatest 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.

Code

Function:

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

Test Code:

SELECT * FROM dbo.udf_GetTopNFilesWithIOStall(10)

SELECT * FROM dbo.udf_GetTopNFilesWithIOStall(20)