sys.dm_io_virtual_file_stats

In SQL 2005, Microsoft introduced the Dynamic Management Views and Functions. They provide a wealth of information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.

One of them is the sys.dm_io_virtual_file_stats. It returns I/O statistics for data and log files.  Since it displays the cumulative values since the last server (re)start, it’s important to measure and compare these values over time.  I have not found any script example on the net that could log and display this information in detail over time. Therefore I’ve written two scripts myself; one to log the data into a table, and one to display the values per sample interval.

First the script for the logging. You can put this in a SQL job.

— Scripts creates a table called ‘io_virtual_file_stats’ (if it does not exist already)

— Script will add a snapshot of sys.dm_io_virtual_file_stats every xx seconds, with a timestamp (tstamp)

 

WHILE 1=1

BEGIN

                IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[io_virtual_file_stats]’) AND type in (N’U’))

                BEGIN

                                select top (0) *, getdate() as tstamp

                                into io_virtual_file_stats

                                from sys.dm_io_virtual_file_stats(NULL,NULL)

                END

 

                declare @tstamp datetime

                set @tstamp = getdate()

 

                insert into io_virtual_file_stats

                select *, @tstamp

                from sys.dm_io_virtual_file_stats(NULL,NULL)

               

                WAITFOR DELAY ’00:00:30′

END

 

Then the script to query the table:

 

 

WITH

 vfs_raw as

 (    select row_number() over (partition by file_handle order by tstamp asc) as row_num, *

            from dbo.io_virtual_file_stats

 )

,vfs as

 (    select

             x2.num_of_reads– x1.num_of_reads as num_of_reads

            ,x2.io_stall_read_ms– x1.io_stall_read_ms as io_stall_read_ms

            ,x2.[sample_ms]– x1.[sample_ms] as [sample_ms]

            ,x2.[num_of_bytes_read]– x1.[num_of_bytes_read] as [num_of_bytes_read]

            ,x2.[num_of_writes] – x1.[num_of_writes] as [num_of_writes]

            ,x2.[num_of_bytes_written] – x1.[num_of_bytes_written] as [num_of_bytes_written]

            ,x2.[io_stall_write_ms] – x1.[io_stall_write_ms] as [io_stall_write_ms]

            ,x2.[io_stall] – x1.[io_stall] as [io_stall]

            ,x2.[size_on_disk_bytes] – x1.[size_on_disk_bytes] as [size_on_disk_bytes]

            ,x2.database_id

            ,x2.file_id

            ,x2.file_handle

            ,x2.tstamp                   

      from vfs_raw as x1

      join vfs_raw as x2

            on x1.row_num = x2.row_num–1 and x1.file_handle = x2.file_handle

 )

SELECT

    –virtual file latency

    vReadLatency

        = CASE WHEN num_of_reads = 0

               THEN 0 ELSE (io_stall_read_ms/num_of_reads) END

  , vWriteLatency

       = CASE WHEN num_of_writes = 0

              THEN 0 ELSE (io_stall_write_ms/num_of_writes) END

  , vLatency

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

             THEN 0 ELSE (io_stall/(num_of_reads + num_of_writes)) END

  –avg bytes per IOP

  , BytesperRead

        = CASE WHEN num_of_reads = 0

               THEN 0 ELSE (num_of_bytes_read/num_of_reads) END

  , BytesperWrite

       = CASE WHEN num_of_writes = 0

              THEN 0 ELSE (num_of_bytes_written/num_of_writes) END

  , BytesperTransfer

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

             THEN 0 ELSE ((num_of_bytes_read+num_of_bytes_written)/(num_of_reads + num_of_writes)) END            

  , LEFT(mf.physical_name,2) as Drive

  , DB_NAME(vfs.database_id) as DB

  , mf.name AS FileName

  , mf.physical_name

  , vfs.* 

from vfs

JOIN sys.master_files as mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id

— WHERE DB_NAME(vfs.database_id) = ‘master’

— WHERE mf.name = ‘mastlog’

–ORDER BY  vLatency desc

ORDER BY  tstamp desc, database_id, file_handle