DMV Queries - 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)

ċ
udf_GetTopNFilesWithIOStall.sql
(3k)
Andy Hughes,
Aug 3, 2012, 11:54 AM
Comments