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
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.
Add this statement and it worked for me
WHERE session_duration >= @longrunningthreshold
AND cte.Session_query NOT LIKE '%sp_server_diagn%'
3 People are following this question.
