Query to find Statistics of physical database files in the SQL Server
Using the below query, we can find the number of reads, writes, amount of data read, written to the database physical files MDF and LDF.
select db_name(mf.database_id) as database_name, GETDATE() Timestamp,
--left(mf.physical_name, 1) as drive_letter,
vfs.num_of_writes, vfs.num_of_bytes_written, vfs.io_stall_write_ms,
mf.type_desc, vfs.num_of_reads, vfs.num_of_bytes_read, vfs.io_stall_read_ms,
vfs.io_stall, vfs.size_on_disk_bytes,mf.physical_name
from sys.master_files mf
join sys.dm_io_virtual_file_stats(NULL, NULL) vfs
on mf.database_id=vfs.database_id and mf.file_id=vfs.file_id and db_name(mf.database_id)='databasename'
order by vfs.num_of_bytes_written desc
