查找耗时的查询

本文介绍了查找耗时的查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我目前正在开展性能提升项目， 我发现了来自sys teble的耗时查询， sys.dm_exec_query_stats， ref： blog.sqlauthority/2010/05/14/sql-server-find-most-昂贵的查询使用-dmv / [ ^ ] 当我在生产服务器上运行此查询时，它返回那些具有的select语句提示nolock在桌子上，并显示total_worker_time（也是total_elapsed_time）大约100-200秒。 我的问题是这是记录或列出的正确方法最耗时的查询?? 如果我错了，请建议我。 如果有的话一个人有另一种方法可以找出最耗时的查询而不是建议我。 非常非常感谢。

Hi, I have currently working on performance enhancement project, and I am finding time consuming queries from sys teble, sys.dm_exec_query_stats, ref:blog.sqlauthority/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/[^] when I run this query on production server it returns those select statements which have hint nolock on table, and shows total_worker_time(also total_elapsed_time) about more than 100-200 secs . My question is that is this correct way to log or list out the most time consuming queries?? If I am going wrong than please suggest me. If any one have another way to find out most time consuming queries than please suggest me. Very Very Thanks.

推荐答案

在您提供的博客上，按读取强度进行排序。实际上这是正确的，因为很可能这样的查询执行得很长。 如果您按照用于获得结果的绝对时间来定位查询，则可能会使用稍微修改过的查询来自您的博文：按qs.last_execution_time排序DESC On a blog you provided, sort is done by reads intensity. Actually this is correct, as most likely such queries execute very long. If you are targeting queries by absolute time used to get the result, you might use slightly modified query from your blog post: order by qs.last_execution_time DESC SELECT TOP 10 qt.text as QUERY, qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.min_logical_reads, qs.max_logical_reads, qs.total_elapsed_time, qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qt.encrypted=0 and qt.text not like ‘/*Debug query */%’ ORDER BY qs.last_execution_time DESC, qs.total_logical_reads DESC

注意：通过奇怪的''调试查询''我们正在消除我们的查询本身。

note: by strange ''Debug query'' we are eliminating our query itself.