how to find long-running queries and send as an email

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