Finding time consuming queries

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008

, +

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 6:03am

ExpertITM

Updated 12-Feb-13 7:22am

Mike Meinz

v2

Add a Solution

Comments

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Vyacheslav Voronenko · Answer 1 · 2013-02-12T08:39:00

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

SQL

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.