Long Running Queries Data....

Question

Long Running Queries Data....

sql_jr

SSCommitted

Points: 1612
More actions
June 28, 2011 at 2:55 pm

#242874

Hi, I am using sys.dm_exec_query_stats to get the duration of the longest running queries, (here top 10) I was wondering, if I can base my "alert" on the following query:
This basic DMV query:
---------------------
SELECT TOP 10
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
DB_NAME(qt.dbid) AS database_name, qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
last_elapsed_time/ 1000000.0 As Last_Elapsed_Time,
qs.execution_count As Execution_Count,
qs.creation_time AS CreatedOn
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
--where qt.dbid = DB_ID()
ORDER BY average_seconds DESC
So, my thinking was, if the last_elapsed_time is greater than the avg elapsed time, this would indicate a "long running query" In other words, its greater than the average.
Does this logic sound correct? Or, would it be if the last_elapsed_time is greater than the "previous" avg_elapsed_time (if I captured it), this would indicate a query taking longer than the average?? I know that each time the row is updated the execution count increments, and the elapsed time is factored into the "new" average.
Thoughts? Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Logic sounds good.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sql_jr SSCommitted Points: 1612 More actions · Answer 2

Thanks. Which part sounds right? The first where last_elapsed > avg_elapsed, or where last_elapsed > ~previous avg_elapsed? THx

trevelvis SSC Veteran Points: 266 More actions · Answer 3

So, my thinking was, if the last_elapsed_time is greater than the avg elapsed time, this would indicate a "long running query" In other words, its greater than the average.

I would go with greater than the average...assuming the execution count is greater than 1;-)

________________________________________________________________________________

Can I ask you a rhetorical question...?
________________________________________________________________________________

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 4

I would also put a buffer in to your calculation so you are not notified every time a query takes a second more than average.

sql_jr SSCommitted Points: 1612 More actions · Answer 5

Thanks, all! Yeah, the rule is where execution count > 1

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

Yes and by definition avg means you have ± 50% over and 50% under the avg. So I would define what over avg really means so you don't get completely flooded with alerts.

Plz post your final script here once you're done.