SQL Server 2008: I/O Wait Time per Database File

Question

I am running SQL Server 2008 Enterprise Edition and want to monitor the following performance metrics i.e. via dynamic management views (from within SQL):

Average/Maximum Read/Write I/O Waits in ms per database file for sliding time window.

That is: 4 numbers per database file: avg read wait, max read wait, avg write wait, max write wait. All in ms, and all for some sane (or even better configurable) sliding time window.

How can I do that?

PS: I have the VIEW SERVER STATE permission and can read sys.dm_os_performance_counters, sys.database_files, sys.dm_io_virtual_file_stats etc etc

PS2: At least 1 tool (Quest Spotlight 7 for SQL Server) is able to provide Max I/O Wait in ms per database file. So there has to be some way ..

Answer 1

Below is the query that SSMS's Activie Monitor uses. They label the io_stall field as total wait time. You could add the fs.io_stall_read_ms and fs.io_stall_write_ms fields to get the read/write specific numbers.

SELECT     
    d.name AS [Database], 
    f.physical_name AS [File], 
    (fs.num_of_bytes_read / 1024.0 / 1024.0) [Total MB Read], 
    (fs.num_of_bytes_written / 1024.0 / 1024.0) AS [Total MB Written], 
    (fs.num_of_reads + fs.num_of_writes) AS [Total I/O Count], 
    fs.io_stall AS [Total I/O Wait Time (ms)], 
    fs.size_on_disk_bytes / 1024 / 1024 AS [Size (MB)],
    fs.io_stall_read_ms
FROM sys.dm_io_virtual_file_stats(default, default) AS fs
INNER JOIN sys.master_files f ON fs.database_id = f.database_id AND fs.file_id = f.file_id
INNER JOIN sys.databases d ON d.database_id = fs.database_id; 

This query only gives you the totals. You'd have to run it at some interval and record the results in a temp table with a time stamp. You could then query this table to get your min/max/avg as needed. The sliding time window would just be a function of how much data you keep in that table and what time period you query.

Answer 2

The problem you are going to have is that SQL doesn't necessarily track the level of detail you are looking to get per file. You are probably going to have to use Performance monitor as well. You will have to use a combination approach looking at both performance monitor for details on I/O on the disk over the course of time. As well as the aforementioned SQL monitoring techniques to see get a more complete complete picture. I hope that helps.