I am trying to find a long running query that happened yesterday. When I look at the long running queries report for different time frames, it doesn't make sense. When I create a report for longest running query, the times don;t match up, If I choose 12 hours, the longest query is 26m. 1 day is 74 min, 2 days is 1012m ... fine so far, but when I choose 3 days the longest running query is 913m.?. 5 is 913m, 7 is 913m, 14 day sis 672 min, 28 is 672m and Month is 1012. I also found I had another tab open which shows 2 days and longest running query is 999m Where is it getting these numbers from? In table format:
Time frame last: Duration of longest running query:
12 hours 26m
1 day 74m
2 days 1012m or 999m
3 days 913m
5 days 913m
7 days 913m
14 days 672m
28 days 672m
Month 1012m
The numbers also seem to change occasionally as I verify what I am writing in this message.
So I try to find the alert for this long running query. I cannot find the alert anywhere. I checked read, unread and cleared and cannot find the query. I also checked dm_exec_query_stats on the SQL server and do not see a query that lasted that long. Here is the full query:
SELECT st.text,
qp.query_plan,
qs.*
FROM (
SELECT TOP 50 *
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.max_worker_time > 300
OR qs.max_elapsed_time > 300
ORDER BY qs.max_worker_time DESC
Can anyone assist me to find this query and the alert it generated?
Thanks,
Cory
Hello Cory,
qp.query_plan,
qs.*
FROM (
SELECT TOP 50 *
FROM sys.dm_exec_query_stats
ORDER BY total_worker_time DESC
) AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE qs.max_worker_time > 300
OR qs.max_elapsed_time > 300
ORDER BY qs.max_worker_time DESC