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

--dadeniji
--get IO Stats at Individual File Levels

select
              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 (

                      select
                           io_handle
                         , 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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s