SQL SERVER 查看IO消耗最多的50条语句

select top 50   

(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, statement_end_offset as stmt_end_offset,  

substring(sql_text.text, (statement_start_offset/2),   

case   

when (statement_end_offset -statement_start_offset)/2 <=0 then 64000  

else (statement_end_offset -statement_start_offset)/2 end) as exec_statement,  sql_text.text,plan_text.*  

from sys.dm_exec_query_stats    

cross apply sys.dm_exec_sql_text(sql_handle) as sql_text  

cross apply sys.dm_exec_query_plan(plan_handle) as plan_text  

order by   

(total_logical_reads + total_logical_writes) /Execution_count Desc