SQL SERVER – Most Used Database Files – Script

SQL Server Performance Tuning is a discovery process. Every day we learn something new with this one. One of the activities we do during  Comprehensive Database Performance Health Check is to check which is the most used database file. This we only do when we suspect that one particular database is the trouble. 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.

SQL SERVER - Most Used Database Files - Script mostused-800x408

Here is the quick video I have built on this topic. I will post it tomorrow. However, here is the script which is used in the video.

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)

Here are my few recent videos and I would like to know what is your feedback about them. Do not forget to subscribe SQL in Sixty Seconds series. I hope you appreciate learning about the last few SQL in Sixty Seconds Video.

If you have any questions, you can always reach out to me on Twitter.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

,
Previous Post
MySQL – Recover Dropped Performance Schema Database

Related Posts

Leave a Reply

Menu