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.

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.

1_SQL_Server_Configure_alert_for_long_running_queries

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.

 

Regards

Ahmad Osama

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook