MS SQL Server database file I/O stats

dm_io_virtual_file_stats.sql

SELECT
    a.io_stall, -- Total time, in milliseconds, that users waited for I/O to be completed on the file.
    a.io_stall_read_ms, -- Total time, in milliseconds, that the users waited for reads issued on the file.
    a.io_stall_write_ms, -- Total time, in milliseconds, that users waited for writes to be completed on the file.
    a.num_of_reads, -- Number of reads issued on the file.
    a.num_of_writes, -- Number of writes made on this file.
--a.sample_ms, a.num_of_bytes_read, a.num_of_bytes_written, a.io_stall_write_ms,
    ( ( a.size_on_disk_bytes / 1024 ) / 1024.0 ) AS size_on_disk_mb,
    db_name(a.database_id) AS dbname,
    b.name,
    a.file_id,
    db_file_type = CASE
                       WHEN a.file_id = 2 THEN 'Log'
                       ELSE 'Data'
                   END,
    UPPER(SUBSTRING(b.physical_name, 1, 2)) AS disk_location
FROM
    sys.dm_io_virtual_file_stats (NULL, NULL) a
JOIN
    sys.master_files b ON a.file_id = b.file_id
    AND a.database_id = b.database_id
ORDER BY
    a.io_stall DESC