Click here to Skip to main content
13,146,498 members (49,245 online)
Rate this:
Please Sign up or sign in to vote.

I have currently working on performance enhancement project,

and I am finding time consuming queries from sys teble,


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.
Posted 12-Feb-13 6:03am
Updated 12-Feb-13 7:22am
Mike Meinz26.7K
Mike Meinz 12-Feb-13 13:39pm
I found the same one that you found at the same link you included in your question. I tried it on my database. The Top 10 queries were all SQL Server queries built into the SQL Server software (i.e. not my queries).

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 2

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

qt.text as QUERY,
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,
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.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web02 | 2.8.170915.1 | Last Updated 12 Feb 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100