View the IO of a database file

IO operations on the data are written to the database file, sys.dm_io_virtual_file_stats Returns I/O statistics for data and log files.

Nullnull }) 

io_stall: Total time, in milliseconds, which users waited for I/O to is completed on the file.

size_on_disk_bytes: Number of bytes used on the disk for this file.

View the IO of the database file and the time to wait for IO to complete

Selectvfs.database_id, VFS.file_id, Mf.type_desc asFile_type, Vfs.sample_ms/ +/ -/ -  asDuration_h, Vfs.num_of_reads asreads, Vfs.num_of_bytes_read/1024x768/1024x768  asREAD_MB, Vfs.num_of_bytes_read/Vfs.num_of_reads/1024x768  asavg_read_kb, Vfs.io_stall_read_ms/Vfs.num_of_reads asAvg_stall_read_ms, Vfs.num_of_writes aswrites, Vfs.num_of_bytes_written/1024x768/1024x768  asWRITTEN_MB, Vfs.num_of_bytes_written/Vfs.num_of_writes/1024x768  asavg_writted_kb, Vfs.io_stall_write_ms/Vfs.num_of_writes asAvg_stall_write_ms, Vfs.io_stall/(vfs.num_of_reads+Vfs.num_of_writes) asAvg_stall_ms,cast(vfs.size_on_disk_bytes/1024x768/1024x768/1024.0  as decimal(Ten,2)) asDISK_SIZE_GB,cast(mf.size/1024x768*8/1024.0  as DECIMAL(Ten,2)) asFILE_SIZE_GB, Vfs.file_handle fromsys.master_files MF CrossAPPLY Sys.dm_io_virtual_file_stats (MF.DATABASE_ID,MF.file_id) asVFSwheremf.database_id=db_id()--Current dbOrder  byAvg_stall_read_msdesc, Avg_stall_write_msdesc

Reference doc:

SYS.DM_IO_VIRTUAL_FILE_STATS (Transact-SQL)

SYS.MASTER_FILES (Transact-SQL)

View the IO of a database file