问题描述
我目前正在开展性能提升项目，
我发现了来自sys teble的耗时查询，
sys.dm_exec_query_stats，
ref： []
当我在生产服务器上运行此查询时，它返回那些具有的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.
推荐答案
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.
这篇关于查找耗时的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！