SQL Agent Job or SQL Alert for Long Running Queries

Question

Am trying to create either an Agent Job that runs scheduled every 60 minutes or a SQL Alert that fires off a mail everytime a query is detected that runs longer than 1 hour. The below is the basis of the query which I have:

SELECT 
    r.session_id as [SPID]
    ,r.[start_time] as [Start Time] 
    , T.[text] as [Query]
FROM sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.[sql_handle]) T
where 
 DATEDIFF(second, r.[start_time], getdate()) > 3600
GO

Would it be more efficient to use an IF Statement, i.e if rows are returned then send mail, if so what is the best way of creating this. Or use a SQL Alert condition that would alert for Long Running Transactions > 3600 seconds and send via operator. If I have go via the Alert would I need an agent job with the Above SQL in. I have tried this (via SQL Alert) but the alert does not seem to trigger.

Any help is much apprecaited.

Have tried to include IF Statement but getting Syntax error (Incorrect syntax near the keyword 'into'.), note made it 60 seconds for testing.

IF (SELECT
'SPID='+CONVERT(VARCHAR,a.session_id)+' has been running the following for '+CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP))+' seconds: '+convert(varchar,b.text), CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP)) 
INTO #LongRunningQuery
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE a.status <> 'background'
AND DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP) > 60) > 0

begin

exec msdb.dbo.sp_send_dbmail

@profile_name = 'Test Alerts',

@recipients = '[email protected]',

@subject = 'Warning',

@query = 'select * from #LongRunningQuery'

DROP TABLE #LongRunningQuery
end

Any help is much apprecaited.

Answer 1

You can't use the INTO keyword inside of an IF clause, so the syntax error you're currently getting is due to trying to insert the results into a temp table as part of the IF clause.

There's no performance benefit stuffing the query inside the IF clause, so for better readability I would run the query first then check the if results exist like so:

-- Get any long running queries
DROP TABLE IF EXISTS #LongRunningQuery
SELECT
    'SPID='+CONVERT(VARCHAR,a.session_id)+' has been running the following for '+CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP))+' seconds: '+convert(varchar,b.text) AS EmailBody, 
    CONVERT(VARCHAR,DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP)) AS Runtime
INTO #LongRunningQuery
FROM sys.dm_exec_requests a
CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) b
WHERE a.status <> 'background'
    AND DATEDIFF(SS, a.start_time, CURRENT_TIMESTAMP) > 60

-- Send email when long run queries found
IF EXISTS (SELECT 1 FROM #LongRunningQuery)
BEGIN

    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Test Alerts', @recipients = '[email protected]', @subject = 'Warning', @query = 'select * from #LongRunningQuery'
    
END