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