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
          0
       ELSE
          io_stall_read_ms/num_of_reads
       END AS AvgIOStallRead_ms,
       CASE WHEN num_of_writes = 0 THEN
          0
       ELSE
          io_stall_write_ms/num_of_writes
       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