查找耗时的查询 [英] Finding time consuming queries

问题描述

我目前正在开展性能提升项目，



我发现了来自sys teble的耗时查询，

sys.dm_exec_query_stats，



ref： http://blog.sqlauthority.com/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:http://blog.sqlauthority.com/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.

这篇关于查找耗时的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！