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.
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Helpful utility Osama.
reat article. I wonder if the SCOM Management Pack has something like this…
Why not configure an SQL Server Alert using the Alerts management interface already present on SQL Server?
Great article, very helpful. tks
Thanks for comments.. will look into the option of creating alerts using sql alert management.
THANK YOU THANK YOU THANK YOU!!!! YOU’RE A LIFE SAVER!! 😀
Thanks 🙂
without scheduling the job..is there any way to grt alert from agent alert….
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.
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.
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
execute the procedure in sql server management studio and paste the resut here.
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
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
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
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
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.
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
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.
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)