March 19, 2024 at 5:25 am
I've been exploring various methods to monitor and alert for long-running queries in SQL Server. I've tried 'extended events', 'resource governor', 'SQL Server Profiler', 'Database Query Analyzer', and even 'Custom Monitoring Scripts'. However, each approach seems to have its limitations.
For instance, with extended events, I can't log events during query execution; it only captures events at the start or completion. Similarly, the Resource Governor doesn't offer a direct mechanism to alert or notify when a query exceeds a specified time threshold. Even with Custom Monitoring Scripts, I'm struggling to identify the actual long-running queries effectively.
My ultimate goal is to send an alert to a user when a query execution surpasses a predefined time limit while it's still in progress. Is there a reliable strategy or toolset beyond the traditional SQL monitoring methods to achieve this? Any insights or suggestions would be greatly appreciated!
March 19, 2024 at 5:46 am
This was removed by the editor as SPAM
March 19, 2024 at 8:29 am
If you are interested in Currently running queries and not already completed queries in cache, I would suggest a job that runs every X seconds that look at current running queries:
SELECT.................. FROM sys.dm_exec_connections ExecConnections
LEFT OUTER JOIN sys.dm_exec_sessions ExecSessions ON ExecConnections.session_id = ExecSessions.session_id
LEFT OUTER JOIN sys.dm_exec_requests ExecRequests ON ExecConnections.connection_id = ExecRequests.connection_id
OUTER APPLY sys.dm_exec_sql_text(ExecRequests.plan_handle) SQLText
and then look at "ExecRequests.total_elapsed_time". If this value is greater than your threshold of Y seconds, send a mail alert with information in SQLText.text
Just and idea.. see if that works for you..
April 17, 2024 at 10:31 am
This was removed by the editor as SPAM
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply