SQL Server: Top 10 IO Queries

This content 7 years old. Please, read this page keeping its age in mind along with the fact technology changes fast and the information on this page me be outdated, not best practice, or plain wrong.

From Sunil’s blog to identify the top 10 queries generating most IO.

 

SELECT TOP 10
    (total_logical_reads/execution_count) AS
                                 avg_logical_reads,
    (total_logical_writes/execution_count) AS
                                 avg_logical_writes,
    (total_physical_reads/execution_count)
                                 AS avg_phys_reads,
    execution_count,
    statement_start_offset as stmt_start_offset,
    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
                ELSE statement_end_offset
            END - statement_start_offset)/2)
     FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
         plan_handle
FROM sys.dm_exec_query_stats
ORDER BY
  (total_logical_reads + total_logical_writes) DESC

Source

Speak Your Mind

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.