question

Askbhuvanesh avatar image
Askbhuvanesh asked

How to exclude some queries in Long running query alerts

Hi, Currently I'm using SQL sever 2012 enterprise edition. I have configured a long running query alert. My SQL server is in **alwayson availability group**. My Problem is this alert sent ***sp_server_diagnostics***  - is long running. I know this is an alwayson related query. So I don't need this alert. My question is **how to exclude this query or this alert from my alert mail**. Here is the query--------------- DECLARE @xml NVARCHAR(max) DECLARE @body NVARCHAR(max) -- specify long running query duration threshold DECLARE @longrunningthreshold INT SET @longrunningthreshold = 61 -- 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(s, ( 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) ,[Complete_Query]= (st.TEXT) ,[program_name]= qs.program_name ,[hostname] = qs.hostname ,[isblocked] = qs.blocked FROM sys.sysprocesses qs CROSS APPLY sys.dm_exec_sql_text(sql_handle) st WHERE qs.lastwaittype<>'BROKER_RECEIVE_WAITFOR' AND qs.cmd NOT LIKE '%BACKUP%' AND qs.cmd NOT LIKE '%INDEX%' AND qs.cmd NOT LIKE '%RESTORE%' AND qs.cmd NOT LIKE '%DBCC%' AND qs.program_name NOT LIKE '%SQLAgent - TSQL JobStep%' AND qs.hostname NOT LIKE '%RPT01%' ) -- 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' ,'' ,[Complete_Query] AS 'td' ,'' ,[program_name] AS 'td' ,'' ,[hostname] AS 'td' ,'' ,[isblocked] 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 (longer than 60 sec) Session_id Duration(sec) Status CurrentQuery CompleteQuery ProgramName Hostname isblocked ' 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 = '*********' ,@body = @body ,@body_format = 'html' ,@recipients = '**********@*******' ,@subject = 'ALERT: Long Running Queries on DBServer'; END
queryalwaysonalerts
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image
KenJ answered
It looks like you are filtering broad buckets from the cte using the sysprocesses.cmd column. If you want to filter a specific query out, you'll need to filter based on the st.text column which holds the query: `st.text not like '%sp_server_diagnostics%'`. You could also place the filter in the outer query where you are filtering on session_duration > @longrunningthreshold. Just use the [session_query] or [complete_query] column and a `not like '%sp_server_diagnostics%' ` filter.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vickyarea51 avatar image
vickyarea51 answered


Add this statement and it worked for me

WHERE session_duration >= @longrunningthreshold
AND cte.Session_query NOT LIKE '%sp_server_diagn%'

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.