Microsoft – SQL Server – IO Stats @ Per each data\Log file

--get IO Stats at Individual File Levels

              db_name(tblFileStat.database_id) as databaseName 
            , tblFileStat.file_id
            , tblMasterFile.physical_name
            , tblIOPending.io_pending
            , tblFileStat.io_stall_read_ms
            , tblFileStat.io_stall_write_ms
            , tblFileStat.file_handle
            , tblIOPending.NumberofIOPendingRows

from sys.dm_io_virtual_file_stats(null, null) tblFileStat

        inner join (

                         , Max(io_pending) as io_pending
                         , count(*) as NumberofIOPendingRows

                      from sys.dm_io_pending_io_requests

                      group by io_handle

                 ) tblIOPending

           on tblFileStat.file_handle = tblIOPending.io_handle

       inner join sys.master_files tblMasterFile

           on tblFileStat.database_id = tblMasterFile.database_id
           and tblFileStat.file_id = tblMasterFile.file_id

order by

         tblFileStat.io_stall_read_ms desc

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s