Most Used Database Files in SQL Server

Hello everyone,

In this article, I will try to give information about finding the most used database files in SQL Server.

In SQL Server, in some cases you may want to find the most used database files.

You can easily do this by using the script below.

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);

When you run the script, you will see a result similar to the one below.

As you can see, the most used database files are listed.

Good luck to everyone in business and life.