Long running queries for SQL Server can cause many problems and it is deadly important to find them and to be informed when something goes wrong. Let's assume that we would like to tune queries lasting more than 60 seconds. We will pass duration as a parameter and you can adapt it to your environment as you wish. The T-SQL script below gets @duration as a parameter choosing the oldest active query running. And if the oldest one is running for more than 60 secs, it lists all the queries running longer than 60 secs.


declare @minstarttime datetime
declare @duration int
select @minstarttime=min(start_time) from sys.dm_exec_requests where session_id>50
--select getdate()
--select @minstarttime
select @duration=datediff (s, @minstarttime, getdate())
--select @duration
if(@duration > 60)
select r.session_id, t.text, x.login_name, x.program_name, datediff(s, start_time, getdate()) as durationSec, p.query_plan
--cross apply sys.dm_exec_sql_text (sql_handle)
from sys.dm_exec_requests r with (nolock)
cross apply sys.dm_exec_sql_text(sql_handle) t
cross apply sys.dm_exec_query_plan(plan_handle) p
inner join sys.dm_exec_sessions x on r.session_id = x.session_id
inner join sys.dm_exec_connections c on c.session_id = x.session_id
where datediff(s, start_time, getdate()) > 60
order by datediff(s, start_time, getdate()) desc

If you have queries running more than 60 secs, the result will be sth similar to the below:

After configuring DB mail, you can run a script which is similar to below in every 60 seconds via SQL Agent job, and then you will be alarmed if you have queries running longer than 60 seconds, that's all!

Within the script, sp_send_dbmail stored procedure is used to send an email. With @tableHTML variable, the T-SQL script is used to detect queries running more than 60 seconds and @recipients variable shows the recipient email address.

declare @minstarttime datetime
declare @duration int
select @minstarttime=min(start_time) from sys.dm_exec_requests where session_id>50
select @duration=datediff (s, @minstarttime, getdate())

if(@duration > 60)
begin
declare @tableHTML NVARCHAR(MAX)
SET @tableHTML =
N'Hello;<br/><br/> On the X server, queries running longer than 60 seconds:<br/>'+
N'<table style="border-collapse:collapse;font-size:10pt;white-space:nowrap;" border="1" cellpadding="10">' +
N'<tr bgcolor="#D6D9E0"><td>session_id</td><td>login_name</td><td>sql_text</td><td>program_name</td><td>Second_Duration</td>' +
N'<td>query_plans</td></tr>' +
CAST ( ( select
td = r.session_id,'',
td = x.login_name,'',
td = t.text,'',
td = x.program_name,'',
td = datediff(s, start_time, getdate()),'',
td = p.query_plan,''
from sys.dm_exec_requests r with (nolock)
cross apply sys.dm_exec_sql_text(sql_handle) t
cross apply sys.dm_exec_query_plan(plan_handle) p
inner join sys.dm_exec_sessions x on r.session_id = x.session_id
inner join sys.dm_exec_connections c on c.session_id = x.session_id
where datediff(s, start_time, getdate()) > 60
order by datediff(s, start_time, getdate()) desc
FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'recipient@xmail.com',
@subject = 'Queries Running Longer Than 60 Seconds',
@body = @tableHTML,
@importance = 'HIGH',
@body_format = 'HTML' ;


end