find which query execuitng taking longer than 10 s

Question

Post reply

find which query execuitng taking longer than 10 s

SQL_Surfer

SSCrazy Eights

Points: 8320
More actions
August 1, 2011 at 1:42 pm

#245383

I know the query from app hits two tables. I need to send an email out if query takes more than 15s to execute.
ANy idea how to figure out the query running more than 15s?
Thanks for your help.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

this will show you any queries that are in the DMV cache that ever had a elapsed time of more than 15 seconds.

you could refine it from there, but this would get you started, at least.

note that items in the DMV can be aged out, so you could miss long running queries that were not in cache; to be complete, Grant Fitchey suggests a server side trace so you can query that in this article:

http://www.sqlservercentral.com/articles/Performance/71549/

USE master;

GO

select

fn.*,

st.*

from sys.dm_exec_query_stats st

cross apply sys.dm_exec_sql_text(st.[sql_handle]) fn

where st.max_elapsed_time >= (1000 * 15) --15 seconds

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!

SQL_Surfer SSCrazy Eights Points: 8320 More actions · Answer 2

Thanks. But I need somthing that is currently executing and it is executing longer than 15 second. Not the ones which aare done execution.

Lowell SSC Guru Points: 323518 More actions · Answer 3

i think you have to wait until something is executed first...it may have completed a second ago, but i don't think the dmv will return what is currently executing, just items that completed.

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!