SQL SERVER – Script to List Database File Latency

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 Name], 
		mf.physical_name [Physical Name], 
		num_of_reads, num_of_bytes_read, io_stall_read_ms, 
		num_of_writes, num_of_bytes_written, io_stall_write_ms, 
		io_stall, size_on_disk_bytes
FROM	sys.dm_io_virtual_file_stats(null,null) AS vfs
INNER JOIN sys.master_files AS mf
              ON mf.database_id = vfs.database_id
                 and mf.file_id = vfs.file_id
-- ORDER BY io_stall DESC

The script above will give you all the databases you have in your instance with their IO Read and IO Write stall. A column (io_stall) contains the addition of the read IO and write IO. You can further order the result of this script in such a way that you can know which file has maximum read and write IO.

Once you know the file which has maximum IO stall, you can do either of the following to improve the performance of the query.  Do not forget to subscribe SQL in Sixty Seconds series.

• Reduce workload on the database file
• Improve overall performance of queries on that database file
• Replace with a faster disk for better IOPS

Let me know what you think of this blog post and the script used in the blog. If you like, I can convert this blog post into a video as well.

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