Today, we will see a very interesting script to list database file latency. I use this script quite often in my Comprehensive Database Performance Health Check.
Most of my client uses multiple drives for their database. I often see their TempDB and Log files on the fastest possible drive whereas they have huge SAN where they keep their data files. During SQL Server Performance Tuning engagement, we often end up in a situation where we have to know which file, in particular, has more latency than others.
Here is the Script to List Database File Latency
SELECT DB_NAME(mf.database_id) AS [Database