Recently, I came across a situation where on one of the server I manage, there was a full text query which took hours to complete and many times caused overall system slowness. It was difficult to monitor the server every time to check whether the query is running or not. Thus I configured an alert to send email whenever a query duration exceeds a threshold (1 minute in my case).
The query to setup the SQL Server Configure alerts is given below.
DECLARE @xml NVARCHAR(max) DECLARE @body NVARCHAR(max) -- specify long running query duration threshold DECLARE @longrunningthreshold int SET @longrunningthreshold=1 -- step 1: collect long running query details. ;WITH cte AS (SELECT [Session_id]=spid, [Sessioin_start_time]=(SELECT start_time FROM sys.dm_exec_requests WHERE spid = session_id), [Session_status]=Ltrim(Rtrim([status])), [Session_Duration]=Datediff(mi, (SELECT start_time FROM sys.dm_exec_requests WHERE spid = session_id), Getdate() ), [Session_query] = Substring (st.text, ( qs.stmt_start / 2 ) + 1, ( ( CASE qs.stmt_end WHEN -1 THEN Datalength(st.text) ELSE qs.stmt_end END - qs.stmt_start ) / 2 ) + 1) FROM sys.sysprocesses qs CROSS apply sys.Dm_exec_sql_text(sql_handle) st) -- step 2: generate html table SELECT @xml = Cast((SELECT session_id AS 'td', '', session_duration AS 'td', '', session_status AS 'td', '', [session_query] AS 'td' FROM cte WHERE session_duration >= @longrunningthreshold FOR xml path('tr'), elements) AS NVARCHAR(max)) -- step 3: do rest of html formatting SET @body = '<html><body><H2>Long Running Queries ( Limit > 1 Minute ) </H2>< table border = 1 BORDERCOLOR="Black"> < tr>< th align="centre"> Session_id </th> <th> Session_Duration(Minute) </th> <th> Session_status </th> <th> Session_query </th></tr>' SET @body = @body + @xml + '</table></body></html>' -- step 4: send email if a long running query is found. IF( @xml IS NOT NULL ) BEGIN EXEC msdb.dbo.Sp_send_dbmail @profile_name = 'your database mail profile', @body = @body, @body_format ='html', @recipients = 'recipients email address', @subject = 'ALERT: Long Running Queries'; END
The explanation is given below.
In step 1, the cte captures the session_id, start time, session duration and the query being executed by the corresponding sessions. The duration is the difference between the session start time and current date time.
In step 2, the result set returned in step 1 is formatted into html table. It encloses the column values in <tr><td>columnvalue</td></tr>.
In step 3, the column header values are added to the result set returned by step 3 and rest of the html formatting is done. This completes the email body in html format.
In step 4, database mail is used to send the html format email to concerned person only if a long running query is found.
A snapshot of the email is shown below.
This completes the alert setup. The above query can be compiled into a stored procedure and can be scheduled to get regular alerts and prevent problems before they occur because of long running queries.