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.

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.

• Optimize DATE in WHERE Clause – SQL in Sixty Seconds #189
• Data Compression for Performance – SQL in Sixty Seconds #188
• Get Current Time Zone – SQL in Sixty Seconds #187
• Detecting Memory Pressure – SQL in Sixty Seconds #186
• CPU Running 100% – SQL in Sixty Seconds #185
• Generate Script of SQL Server Objects – SQL in Sixty Seconds #184
• Prevent Unauthorized Index Modifications – SQL in Sixty Seconds #183
• MAX Columns Ever Existed in Table – SQL in Sixty Seconds #182
• Tuning Query Cost 100% – SQL in Sixty Seconds #181
• Queries Using Specific Index – SQL in Sixty Seconds #180
• Read Only Tables – Is it Possible? – SQL in Sixty Seconds #179
• One Scan for 3 Count Sum – SQL in Sixty Seconds #178
• SUM(1) vs COUNT(1) Performance Battle – SQL in Sixty Seconds #177
• COUNT(*) and COUNT(1): Performance Battle – SQL in Sixty Seconds #176

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

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