The sys.dm_io_virtual_file_stats dynamic management function returns I/O statistics for data and log files [MDF and LDF file], with two parameters, one for database_id and another for the file_id. This function will help you to identify I/O file level.

I was led here when doing a search because we were getting enormous amounts of disk queuing the other day during a round of testing, so I started hunting around, looking for some information on some query to get this information from SQL Server, and I found this article from Itzik Ben-Gan entitled Query DMFs to Analyze Performance Stats.  His stuff is always good, if some of it blows right past my non-mathematical brain quite often (as does a bit of that article) and it pointed me to some queries that are useful, but I also wanted to be able to take a “point in time” reading that I could compare with.  This information, coupled with stuff from BOL here got me going. 

It returns its readings since when the SQL Servers was started. I was wanting to use it to determine hotspots in the filesystem during some very heavy processing times, so I built a little snippet of code to create a table (based on the value of a variable) so you compare some segment of time.

declare @resetbaseLine bit
set @resetbaseLine = 0

set nocount on
if @resetBaseLine = 1 or object_id(‘tempdb..#baseline’) is null
    begin
        if object_id(‘tempdb..#baseline’) is not null
            drop table #baseline
        –initialize the baseline table
        select db_name(mf.database_id) as databaseName, mf.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,
                    getdate() as baselineDate
        into #baseline
        from sys.dm_io_virtual_file_stats(null,null) as divfs
                    join sys.master_files as mf
                        on mf.database_id = divfs.database_id
                             and mf.file_id = divfs.file_id
    end

–output the values, subtracting the baseline from the “currentLine” values 🙂
select currentLine.databaseName, left(currentLine.physical_name,1) as drive
        ,currentLine.physical_name as file_name
        ,currentLine.io_stall – #baseline.io_stall as io_stall
        ,currentLine.io_stall_read_ms – #baseline.io_stall_read_ms as io_stall_read_ms
        ,currentLine.io_stall_write_ms – #baseline.io_stall_write_ms as io_stall_write_ms
        ,currentLine.num_of_reads – #baseline.num_of_reads as num_of_reads
        ,currentLine.num_of_bytes_read – #baseline.num_of_bytes_read as num_of_bytes_read
        ,currentLine.num_of_writes – #baseline.num_of_writes as num_of_writes
        ,currentLine.num_of_bytes_written – #baseline.num_of_bytes_written as num_of_bytes_written
        ,currentLine.size_on_disk_bytes – #baseline.size_on_disk_bytes as size_change_on_disk_in_bytes
        , dateDiff(second,#baseline.baselineDate,currentLine.baselineDate) as seconds_since_baseline
from (
            select db_name(mf.database_id) as databaseName, mf.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,
                    getdate() as baselineDate
            from sys.dm_io_virtual_file_stats(null,null) as divfs
                        join sys.master_files as mf
                                on mf.database_id = divfs.database_id
                                    and mf.file_id = divfs.file_id) as currentLine
                        join #baseline
                                on #baseLine.databaseName = currentLine.databaseName
                                    and #baseLine.physical_name = currentLine.physical_name
            order by currentLine.io_stall – #baseline.io_stall desc

The query returns the following, offset from when the baseline was taken.

• databaseName – name of the database the file is located in
• drive – the drive the file is located on (gives you the ability to sort/group by it)
• file_name – the name of the file from that part of the database is located on
• io_stall – total time in milliseconds that users waited for I/O activity to the file
• io_stall_read_ms – total time in milliseconds that users waited for read to the file
• io_stall_write_ms – total time in milliseconds that users waited for writes to the file
• num_of_reads – total number of reads issued to the file
• num_of_bytes_read – total number of bytes read from the file
• num_of_writes – total number of writes issued to the file
• num_of_bytes_written – total number of bytes written to the file
• size_change_on_disk_in_bytes – change in size of the file
• seconds_since_baseline – seconds since the baseline readings were taken

The only real downside in this query is that it will show you almost too much information about your hotspots and you may have to go fix things!

Edit: added num_of_writes and fixed the num_of_bytes_written

Crossposted to: drsql.spaces.live.com