SQL: Looking at File IO
Author: jason
Date: 2014-01-22 10:26:18
Category: Musing

SQL records the number of writes, reads, etc that it pushes out to its database and log files. Below is a script that helps identify file usage. I commented out the mf.physical_name column, which is the whole path to the physical file. In its place I added a File Name column. Feel free to uncomment the mf.physical_name.


SELECT mf.database_id, mf.file_id, db_name(mf.database_id) as database_name, --mf.physical_name,
RIGHT(mf.physical_name, (PATINDEX('%\%', REVERSE(mf.physical_name)))-1) AS [file_name],
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
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
ORDER BY vfs.num_of_bytes_written DESC

jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009