Most Used Database Files - MSSQL

If the client has multiple databases, this script also helps us to find out which database and particularly which database file is the most used.

/*************Query **************************/

SELECT

DB_NAME(dbid) 'Database Name',

physical_name 'File Location',

NumberReads 'Number of Reads',

BytesRead 'Bytes Read',

NumberWrites 'Number of Writes',

BytesWritten 'Bytes Written',

IoStallReadMS 'IO Stall Read',

IoStallWriteMS 'IO Stall Write',

IoStallMS as 'Total IO Stall (ms)'

FROM

fn_virtualfilestats(NULL,NULL) fs INNER JOIN

sys.master_files mf ON fs.dbid = mf.database_id

AND fs.fileid = mf.file_id

ORDER BY

DB_NAME(dbid)

/***************************************/

Knowledge Sharing