5 Most I/O Intensive Queries.

Syntax

Lists the top 5 statements by average input/output usage


        SELECT TOP(5)
        spName = OBJECT_NAME(QT.objectid)
        ,AvgIO = (QS.total_logical_reads + QS.total_logical_writes) /QS.execution_count
        ,QueryText =
          SUBSTRING(QT.[text],QS.statement_start_offset/2,
          (
            CASE
              WHEN QS.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), QT.[text])) * 2
              ELSE QS.statement_end_offset
            END - QS.statement_start_offset)/2
          )
        FROM
        [master].sys.dm_exec_query_stats QS WITH (NOLOCK)
        CROSS APPLY
        [master].sys.dm_exec_sql_text(QS.sql_handle) QT
        ORDER BY
        AvgIO DESC OPTION (RECOMPILE)
        ;