sql server查询IO消耗大的排查sql诊断语句

S3_Leo

1. select top 50
   
2.     (total_logical_reads/execution_count) as avg_logical_reads,
   
3.     (total_logical_writes/execution_count) as avg_logical_writes,
   
4.     (total_physical_reads/execution_count) as avg_phys_reads,
   
5.      Execution_count,
   
6.     statement_start_offset as stmt_start_offset, statement_end_offset as stmt_end_offset,
   
7. substring(sql_text.text, (statement_start_offset/2),
   
8. case
   
9. when (statement_end_offset -statement_start_offset)/2 <=0 then 64000
   
10. else (statement_end_offset -statement_start_offset)/2 end) as exec_statement,  sql_text.text,plan_text.*
    
11. from sys.dm_exec_query_stats  
    
12. cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
    
13. cross apply sys.dm_exec_query_plan(plan_handle) as plan_text
    
14. order by
    
15. --(total_logical_reads + total_logical_writes) /Execution_count Desc
    
16. (total_logical_reads + total_physical_reads) /Execution_count Desc

复制代码