How Fast and Robust Is Your Storage? - Professional SQL Server 2005 Performance Tuning - page 421

Table 12-3: More Output from sys.dm_io_virtual_file_stats

database_id file_id sample_ms num_of_reads num_of_bytes_read

2 1 − 1568482343 21266054 9.18039E + 11

2 2 − 1568482343 1164 70148096

2 4 − 1568482343 21145932 9.17155E + 11

Io_stall_read_ms num_of_writes num_of_bytes_written io_stall_write_ms

+

1596933746 23124688 1.32502E 12 2342777357

62562 3698286 2.27204E + 11 39249458

1680479682 23145483 1.32687E + 12 2351254446

Io_stall size_on_disk_bytes file_handle

3939711103 31457280000 0x000007D0

39312020 31457280000 0x00000704

4031734128 20971520000 0x000007D4

Here is a simple example that shows the use of a temporary table and WAITFOR to show the change in
the values for a given file over a 10 second period:

-- Capture sample 1
Select *, 1 as sample, getdate() as sample_time
into #dmv_samples
from sys.dm_io_virtual_file_stats (DB_ID('tempdb'),null)

-- Wait for some interval, im using 10 seconds, or just run manually
whenever you want to see whats changed?
waitfor delay '00:00:10'

-- Capture sample 2
insert #dmv_samples
Select *, 2 , getdate()
from sys.dm_io_virtual_file_stats (DB_ID('tempdb'),null)

-- Find the difference between sample 1 and 2
-- Not all columns change, so we only want deltas for
-- num_of_reads
-- num_of_bytes_read
-- io_stall_read_ms
-- num_of_writes
-- num_of_bytes_written
-- io_stall_write_ms
-- io_stall