SQL Server 查询IO消耗大的排查SQL诊断语句

-- 查找平均IO消耗最高的前10个查询
SELECT TOP 10
    (total_logical_reads + total_logical_writes) / execution_count AS [Avg IO],
    (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 Physical Reads],
    execution_count AS [Execution Count],
    SUBSTRING(qt.text, 
        (qs.statement_start_offset/2) + 1,
        ((CASE qs.statement_end_offset 
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset 
         END - qs.statement_start_offset)/2) + 1
    ) AS [Query Text],
    qt.text AS [Full Query Text],
    DB_NAME(qt.dbid) AS [Database Name]
FROM 
    sys.dm_exec_query_stats qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY 
    [Avg IO] DESC