SELECT master_files.NAME, master_files.type_desc, master_files.physical_name, vfs.sample_ms, vfs.num_of_bytes_written, num_of_bytes_read, size_on_disk_bytes/1024/1024/1024 AS Size_in_GB FROM sys.dm_io_virtual_file_stats(null, null) vfs INNER JOIN MASTER.sys.master_files ON vfs.[file_id] = master_files.[file_id] AND vfs.database_id = master_files.database_id ORDER BY (num_of_bytes_read + num_of_bytes_written) desc
Wednesday, October 28, 2009
Short and simple. Uses the DMV to tell you how much IO each file is using. What you think may be the case is not necessarily the case. And remember that backups count. I'd recommend doing 2+ samples during the day, and comparing. That'll help remove the backups from the end totals. Sample_MS is the number of milliseconds the computer has been up.