
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
One comment
Your formatting seems to be wrong.
Daniel Adeniji