how to Find the most critical queries

Question

how to Find the most critical queries

peace2007

SSCrazy

Points: 2154
More actions
February 15, 2009 at 12:30 am

#218470

I need to find queries that use the highest amount of memory or are executed many times.
Could anyone tell me please how I can find those?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you really need to start with creating a trace with profiler....create one and let it run for a while...I'd say one business day.

with that, it will give you every query, as well as the execution time for every query that was run...and you can manipulate it to see which queries were called the most., which were the slowest, etc.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kladibeeto SSCommitted Points: 1582 More actions · Answer 2

Hi,

Use BOL and try to find something about sys.dm_exec_query_stats

bjhogan SSC Eights! Points: 870 More actions · Answer 3

See if this query gives you what you are looking for. I have found ordering by execution_count useful in the past especially for those queries that might execute hundreds of thousands of times per day where an increase of a half second is a huge performance impact.

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.text)

ELSE qs.statement_end_offset

END - qs.statement_start_offset)/2)+1),

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

ORDER BY qs.total_logical_reads DESC

--Ben

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Well half a second times 250 000 executions = 34 hours of processing.

Improvement here is an understatement !!!!

bjhogan SSC Eights! Points: 870 More actions · Answer 5

I wish it stopped there. I like the ones where the application has an hourly job that takes 1.5 hours to run. For some reasons, the developers never see that as a problem...hmmm. I guess peformance really is subjective.

--Ben

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

What happens if the job doesn't run??? I think that goes beyond performance!

peace2007 SSCrazy Points: 2154 More actions · Answer 7

Lowell (2/15/2009)
you really need to start with creating a trace with profiler....create one and let it run for a while...I'd say one business day.
with that, it will give you every query, as well as the execution time for every query that was run...and you can manipulate it to see which queries were called the most., which were the slowest, etc.

SHould I use any special profiler template? or standard template will suffice? what about events?

peace2007 SSCrazy Points: 2154 More actions · Answer 8

Lowell (2/15/2009)
you really need to start with creating a trace with profiler....create one and let it run for a while...I'd say one business day.
with that, it will give you every query, as well as the execution time for every query that was run...and you can manipulate it to see which queries were called the most., which were the slowest, etc.

SHould I use any special profiler template? or standard template will suffice? what about events?

peace2007 SSCrazy Points: 2154 More actions · Answer 9

bjhogan (2/16/2009)
See if this query gives you what you are looking for. I have found ordering by execution_count useful in the past especially for those queries that might execute hundreds of thousands of times per day where an increase of a half second is a huge performance impact.
SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
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
ORDER BY qs.total_logical_reads DESC
--Ben

Thanks I'll try that