See Also: Transact SQL Code Library

Here's how to find out which data and log files have had the most reads or writes. There's also a line to filter by database name - remove the two minus signs before the WHERE clause, and you can put in your own database name.

SELECT DB_NAME(mf.database_id) AS databaseName
,mf.physical_name
,num_of_reads
,num_of_bytes_read
,num_of_writes
,num_of_bytes_written
,size_on_disk_bytes
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.file_id = divfs.file_id
--WHERE DB_NAME(mf.database_id) = 'tempdb'
ORDER BY 1, 3 DESC

Query Test Checklist

• Works on SQL Server 2008: Yes
• Works on SQL Server 2005: Yes
• Works on SQL Server 2000: No
• Works on Standard Edition: Yes
• Works on case-sensitive servers: Yes

Tests Updated by Brent Ozar, 2010-03-03