pointers, solutions and scripts for the SQL DBA
not intended to replace common sense


Capture File IO

Here is a DMV query you can use to examine file IO. Run and store this before every restart.

The num reads and num bytes read columns reset when the sql service resets.

The sample_ms resets when the actual box resets.

select sf.fileid, sf.name, sf.filename

, days_server_up = f.sample_ms/1000./60./60./24.

, f.num_of_reads, gb_read = f.num_of_bytes_read/1024./1024./1024., hours_stall_read = f.io_stall_read_ms/1000./60./60.

, f.num_of_writes, gb_written = f.num_of_bytes_written/1024./1024./1024., hours_stall_write = f.io_stall_write_ms/1000./60./60.

, f.io_stall, size_gb = f.size_on_disk_bytes/1024./1024./1024.

from sys.dm_io_virtual_file_stats (db_id(),null) f

inner join sysfiles sf on f.file_id = sf.fileid