SQL Server Configure alerts for long running queries

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.

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.

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

   

20 Comments on “SQL Server Configure alerts for long running queries”

  1. Why not configure an SQL Server Alert using the Alerts management interface already present on SQL Server?

  2. Thanks for comments.. will look into the option of creating alerts using sql alert management.

  3. Hi Dastagiri – the job scheduled is to execute the procedure at regular interval and find long running queries.. you can have a procedure running in endless loop or a task in task scheduler or a powershell/.net utility to do the same.

  4. The output is not coming in proper format as it has been shown above. I’m getting the correct output but it is coming in scattered format. Please can someone help.

  5. I’m getting the output in the below format:

    Long Running Queries ( Limit > 1 Minute )
    Session_id Session_Duration(Minute) Session_status Session_query 760runnableWITH 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 –DECLARE @xml nvarchar(max) 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

  6. I created the stored procedure, restarted the server to make sure the Stored procedure would take place, do I have to create a job to call it every certain interval or will it run automatically? here is how I created the stored procedure.
    Thank you in advance

    USE [master]
    GO

    /****** Object: StoredProcedure [dbo].[long_running_query] Script Date: 05/28/2015 12:34:12 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE PROCEDURE [dbo].[long_running_query] AS

    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 =
    ‘Long Running Queries ( Limit > 1 Minute ) Session_id Session_Duration(Minute) Session_status Session_query ‘
    SET @body = @body + @xml + ”

    — 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 = ‘mailprofile’,
    @body = @body,
    @body_format =’html’,
    @recipients = ‘example@example.com’,
    @subject = ‘ALERT: Long Running Queries ‘;
    END

    GO

    EXEC sp_procoption N'[dbo].[long_running_query]’, ‘startup’, ‘1’

    GO

  7. Hi Robert – Yes you need to schedule the procedure to get constant updates. The sp_procoption will only run it once when sql instance starts.

    Regards,
    Ahmad

  8. Hi Ahmad ,
    Thanks for coming up with nice solution. somehow i am trying and i am not getting result in e-mail in table format.
    sending format like below…
    do need to make any change sin code?
    Thanks
    Long Running Queries ( Limit > 2 Minute )
    Session_id Session_Duration(Minute) Session_status Session_query 10477724suspendedsp_server_diagnostics11724suspendedWAITFOR (Receive convert(xml,message_body), conversation_handle from Queue_mid10_124_52_24_pid4688_adid2_r8123063), TIMEOUT 30000011954suspendedWAITFOR (Receive convert(xml,message_body), conversation_handle from Queue_mid10_124_52_23_pid2324_adid2_r210447435), TIMEOUT 300000120117suspended

  9. Ahmad,

    I am running it on sql 2012 and getting below output in e mail and not showing any query details.Pls help

    Long Running Queries ( Limit > 1 Minute )
    Session_id Session_Duration(Minute) Session_status Session_query 5135992suspendedsp_server_diagnostics1968suspendedWAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout — Check if there was some error in reading from queue

    1. Anti-virus programs will sometimes “break” multi-part email templates up and cause them to display as raw HTML code.
      This is not a common issue, but to fix this problem you need to change your anti-virus setting so that it accepts multi-part email messages. If you need more guidance, contact your anti-virus software company directly.

      If you configure this alert to your gmail id it will display the output in table format. Hope this will clarify everyone’s doubt.

    2. I have corrected the formatting issue.
      Here is the updated code.

      DECLARE @xml NVARCHAR(max)
      DECLARE @body NVARCHAR(max)
      — specify long running query duration threshold
      DECLARE @longrunningthreshold int
      SET @longrunningthreshold=0
      — 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 d.session_id AS ‘td’,
      ”,
      d.session_duration AS ‘td’,
      ”,
      d.session_status AS ‘td’,
      ”,
      d.session_query AS ‘td’
      FROM cte d
      WHERE session_duration >= @longrunningthreshold
      FOR xml RAW(‘tr’), elements) AS NVARCHAR(max))

      — step 3: do rest of html formatting
      SET @body =
      ‘Long Running Queries ( Limit > 1 Minute ) Session_id Session_Duration(Minute) Session_status Session_query ‘
      SET @body = @body + @xml + ”

      — 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 = ‘mailprofile’,
      @body = @body,
      @body_format =’html’,
      @recipients = ‘example@example.com’,
      @subject = ‘ALERT: Long Running Queries ‘;
      END

  10. Looks like the code did not format properly in my last post.
    I am not sure what tags I need to use to post code.
    If someone can tell me how, I will repost.

  11. report is receiving like below :
    result is

    Long Running Queries ( Limit > 1 Minute ) Session_id Session_Duration(Minute) Session_status Session_query188399suspendedsp_server_diagnostics2420runnableWITH 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 d.session_id AS ‘td’, ”, d.session_duration AS ‘td’, ”, d.session_status AS ‘td’, ”, d.session_query AS ‘td’ FROM cte d WHERE session_duration >= @longrunningthreshold FOR xml RAW(‘tr’), elements) AS NVARCHAR(max))2441suspendedWAITFOR(RECEIVE conversation_handle, service_contract_name, message_type_name, message_body FROM ExternalMailQueue INTO @msgs), TIMEOUT @rec_timeout4260suspendedINSERT INTO “”..”” (“NodeID”,”LastDiscovery”,”FirstDiscovery”,”Missing”,”FlashFileName”,”FlashFileSize”,”FlashCheckSum”,”FlashFileStatus”) VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8)

Leave a Reply

Your email address will not be published.