Configured an alert to send email for long running queries

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 alert is given below.

01 DECLARE @xml NVARCHAR(max)
02 DECLARE @body NVARCHAR(max)
03 -- specify long running query duration threshold
04 DECLARE @longrunningthreshold int
05 SET @longrunningthreshold=1
06 -- step 1: collect long running query details.
07 ;WITH cte
08 AS (SELECT [Session_id]=spid,
09 [Sessioin_start_time]=(SELECT start_time
10 FROM sys.dm_exec_requests
11 WHERE spid = session_id),
12 [Session_status]=Ltrim(Rtrim([status])),
13 [Session_Duration]=Datediff(mi, (SELECT start_time
14 FROM sys.dm_exec_requests
15 WHERE spid = session_id),
16 Getdate()
17 ),
18 [Session_query] = Substring (st.text, ( qs.stmt_start / 2 ) + 1,
19 ( ( CASE qs.stmt_end
20 WHEN -1
21 THEN
22 Datalength(st.text)
23 ELSE qs.stmt_end
24 END
25 -
26 qs.stmt_start ) / 2 ) +
27 1)
28 FROM sys.sysprocesses qs
29 CROSS apply sys.Dm_exec_sql_text(sql_handle) st)
30
31 -- step 2: generate html table
32 SELECT @xml = Cast((SELECT session_id AS 'td',
33 '',
34 session_duration AS 'td',
35 '',
36 session_status AS 'td',
37 '',
38 [session_query] AS 'td'
39 FROM cte
40 WHERE session_duration >= @longrunningthreshold
41 FOR xml path('tr'), elements) AS NVARCHAR(max))
42
43 -- step 3: do rest of html formatting
44 SET @body =
45 '<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>'
46 SET @body = @body + @xml + '</table></body></html>'
47
48 -- step 4: send email if a long running query is found.
49 IF( @xml IS NOT NULL )
50 BEGIN
51 EXEC msdb.dbo.Sp_send_dbmail
52 @profile_name = 'your database mail profile',
53 @body = @body,
54 @body_format ='html',
55 @recipients = 'recipients email address',
56 @subject = 'ALERT: Long Running Queries';
57 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.
Long running queries email alert
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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s