Text in Long Running Query Alert - Last statement to run?
SEarle86
Posts: 21 Bronze 1
I have a long running query alert telling me a query has run for 3675 seconds:
However, when I find the query in the Server overview, I find that the query only took 67 seconds, and query store seems to back this up
Am I right in my assumption that the SQL process fragment uses sys.dm_exec_query_text which returns the text of the current running query in the session?
In the case of the AdventureWorks example below,
Running the below in a separate window while the top 1000001 query is running, returns the session start time and the CURRENT running query text
However, when I find the query in the Server overview, I find that the query only took 67 seconds, and query store seems to back this up
Am I right in my assumption that the SQL process fragment uses sys.dm_exec_query_text which returns the text of the current running query in the session?
In the case of the AdventureWorks example below,
SELECT TOP 1000000 * FROM Person.Address a
CROSS JOIN Person.Address b
CROSS JOIN Person.Address c /* long runner */
GO
SELECT TOP 1000001 * FROM Person.Address a
CROSS JOIN Person.Address b
CROSS JOIN Person.Address c /* long runner */
SELECT s.login_time,
t.text
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(sql_handle) t
WHERE s.session_id = 68
so in theory, if my long running query alert threshold is 1 hour and query 1 has taken 55 mins, query 2 takes 10 mins the alert is raised at 60mins and shows the SQL fragment as query 2?
Is anyone able to clarify if this or similar is what is happening? I appreciate this is a SQL Server level "limitation"(?) but just good to be aware of
so in theory, if my long running query alert threshold is 1 hour and query 1 has taken 55 mins, query 2 takes 10 mins the alert is raised at 60mins and shows the SQL fragment as query 2?
Is anyone able to clarify if this or similar is what is happening? I appreciate this is a SQL Server level "limitation"(?) but just good to be aware of
Tagged:
Answers
Thank you for your post. Query duration is the total time the query ran for according to a combination of dm_exec_ dmvs (largely sys.dm_exec_query_stats).
I've also tried to reproduce the same behaviour using the example you provide for AdventureWorks, but the alert doesn't trigger, even if the first and second query in combination run over the threshold.
If you were to simulate a LRQ using WAITFOR DELAY do you see the same result?
I just ran the AdventureWorks example in management studio to demonstrate that sys.dm_exec_sql_text returns the most recent command in the session, regardless of whether that was the long running command or not
What is populating the SQL Process Fragment section please?