SQL Server identify different queries from a session

Question

I wish to send an alert on long running queries for every 60 seconds the query accumulates. I have a query to serve this using

SELECT some_columns
FROM sys.dm_exec_requests er
INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE DATEDIFF(SECOND, er.start_time, SYSDATETIME()) > 59/* etc... */

My plan is to run this query every second to catch when a query goes from 59 seconds to 60 seconds. I then want to send an alert, but I don't want the same alert every second thereafter, I want to send it every minute thereafter.

I don't want to check for queries running exactly 1 min duration, 2 mins etc. because what if the agent job doesn't run, hence queue approach. My issue is checking if the same query is still running. When I get a match, I store it in a table with the insert time and send the first alert. I then continue to query what's running over 60 seconds and I only want to match that when the last alert time is also > 59 seconds. I have tried matching er.session_id, er.request_id, er.transaction_id but if a session runs a query for 61 seconds followed by a set of queries under the threshold, it alerts as if one very long running query is running for the full duration. It seems to work by comparing er.session_id & er.start_time but I feel like I'm missing the proper identifier.

I'm sure my approach is flawed and I'm trying to solve something already done 1000 times so I'd appreciate any wisdom. Also, this is for specific logins so its not as bad as it may seem.

I am using SQL Server 2019 Standard Edition. Thanks

Answer 1

Since you're saving things in a table, you could save the minutes the query is running using something like this:

DATEDIFF(MINUTE, er.start_time, SYSDATETIME())

With that value registered, you can compare if the value returned on the verification is bigger than the one you saved on the table. See the results from the queries below:

DECLARE @DATE_01 datetime = '2024-04-19 17:14:00.000';
DECLARE @DATE_02 datetime = '2024-04-19 17:14:59.000';
DECLARE @DATE_03 datetime = '2024-04-19 17:15:00.000';
DECLARE @DATE_04 datetime = '2024-04-19 17:15:01.000';

SELECT DATEDIFF(MINUTE, @DATE_01, @DATE_02) AS MINUTES_PASSED; --no alert
SELECT DATEDIFF(MINUTE, @DATE_01, @DATE_03) AS MINUTES_PASSED; --alert
SELECT DATEDIFF(MINUTE, @DATE_01, @DATE_04) AS MINUTES_PASSED; --no alert

Output:

MINUTES_PASSED
--------------
0

(1 row affected)

MINUTES_PASSED
--------------
1

(1 row affected)

MINUTES_PASSED
--------------
1

(1 row affected)

Notice that with that method, in case the agent job doesn't run as you worry, the next second it runs you'll get notified as desired.

Your query could look something like this:

SELECT er.session_id, er.start_time, er.query_hash,
    DATEDIFF(MINUTE, er.start_time, SYSDATETIME()) AS minutes_elapsed
FROM sys.dm_exec_requests er
    INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE DATEDIFF(SECOND, er.start_time, SYSDATETIME()) > 59;