当前位置:首页 > 开发 > 数据库 > 正文

在SQLSERVER中查找消耗IO最多的SQL

发表于: 2015-04-27   作者:357029540   来源:转载   浏览:
摘要: 返回做IO数目最多的50条语句以及它们的执行计划。 select top 50   (total_logical_reads/execution_count) as avg_logical_reads,  (total_logical_writes/execution_count) as avg_logical_writes,  (tot
返回做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

在SQLSERVER中查找消耗IO最多的SQL

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号