Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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.
Posted 12-Feb-13 7:03am
ExpertITM1.2K
Edited 12-Feb-13 8:22am
Mike Meinz23.2K
v2
Comments
Mike Meinz at 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
good
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
 

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.
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 275
1 OriginalGriff 230
2 BillWoodruff 173
3 CPallini 155
4 Kamal Rocks 144
0 OriginalGriff 5,655
1 DamithSL 4,506
2 Maciej Los 3,997
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,180


Advertise | Privacy | Mobile
Web01 | 2.8.141216.1 | Last Updated 12 Feb 2013
Copyright © CodeProject, 1999-2014
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