Getting file usage stats

SELECT db_name(database_id) AS DBName,
       FILE_ID, -- File ID 2 = Log, File Id = 1 Data
       CASE WHEN num_of_reads = 0 THEN
       END AS AvgIOStallRead_ms,
       CASE WHEN num_of_writes = 0 THEN
       END AS AvgIOStallWrites_ms
  FROM sys.dm_io_virtual_file_stats(null,null)

You can use this DMV to see what SQL Server thinks about the I/O stall times. I don’t rely on it 100% but it helpful to see which file is generating the most I/O or is performing the worst. However if you have Avg IO Stall time for read or write over 100 ms, I think it requires investigation. Correlate these with the Disk KPIs such as Avg Disk Sec/Read and Avg. Disk Sec/Write.

References: #9, #10, #11

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.