This dynamic management function returns accumulated I/O statistics for data and log files, it is a replacement for the earlier system function FN_VIRTUALFILESTATS.

Note that the figures include both system and user activity, for example, performing a backup will cause a large number of reads against the files being backed up - bear this in mind when looking for I/O heavy user activity.

select
   db_name(vfs.database_id) as database_name,
   --vfs.file_id,
   mf.name as logical_file_name,
   --vfs.sample_ms,

   vfs.num_of_reads,
   --vfs.num_of_bytes_read,
   --vfs.io_stall_read_ms,
   case when (vfs.num_of_reads = 0) then null else (vfs.num_of_bytes_read / vfs.num_of_reads) end as avg_bytes_per_read,
   case when (vfs.num_of_reads = 0) then null else (vfs.io_stall_read_ms / vfs.num_of_reads) end as avg_stall_per_read,
   case when (vfs.num_of_bytes_read / 1048576 = 0) then null else (vfs.io_stall_read_ms / (vfs.num_of_bytes_read / 1048576)) end as avg_stall_per_MB_read,

   vfs.num_of_writes,
   --vfs.num_of_bytes_written,
   --vfs.io_stall_write_ms,
   case when (vfs.num_of_writes = 0) then null else (num_of_bytes_written / vfs.num_of_writes) end as avg_bytes_per_write,
   case when (vfs.num_of_writes = 0) then null else (io_stall_write_ms / vfs.num_of_writes) end as avg_stall_per_write,
   case when (vfs.num_of_bytes_written / 1048576 = 0) then null else (vfs.io_stall_write_ms / (vfs.num_of_bytes_written / 1048576)) end as avg_stall_per_MB_written,

   case when (vfs.num_of_writes = 0) then null else (vfs.num_of_reads / vfs.num_of_writes) end as reads_to_writes_ratio,
   case when (vfs.num_of_bytes_written = 0) then null else (vfs.num_of_bytes_read / vfs.num_of_bytes_written) end as read_bytes_to_written_bytes_ratio

   --vfs.io_stall, -- = io_stall_read_ms + io_stall_write_ms.
   --vfs.size_on_disk_bytes,
   --vfs.file_handle
from
   sys.dm_io_virtual_file_stats(null,null) vfs
      left join sys.master_files mf on mf.database_id = vfs.database_id
                                   and mf.file_id = vfs.file_id
order by
   vfs.database_id,
   vfs.file_id