cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Canned Alert for Long running SQL excluding

Canned Alert for Long running SQL excluding

I had to create this custom alert and I feel should be an out of the box basic alert that should be included in the product.

SELECT Distinct

'LoginName: '+RTRIM(CONVERT(VARCHAR(50),s.loginame))+', Hostname: '+RTRIM(CONVERT(VARCHAR(50),s.hostname))+', ProgramName: '+RTRIM(CONVERT(VARCHAR(200),s.program_name))+', SPID: '+RTRIM(CONVERT(VARCHAR,s.spid))

--+char(10)+'Duration: '+CONVERT(VARCHAR,DATEDIFF(SS, r.start_time, CURRENT_TIMESTAMP))+' seconds.'+char(10)

+CHAR(10)+convert(varchar(MAX),b.text)

, CONVERT(VARCHAR,DATEDIFF(SS, r.start_time, CURRENT_TIMESTAMP))

FROM master..sysprocesses as s with(NOLOCK)

LEFT OUTER JOIN sys.dm_exec_requests AS r WITH(NOLOCK) 

   ON (s.spid = r.session_id)

CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) b

WHERE r.status <> 'background'

AND DATEDIFF(SS, r.start_time, CURRENT_TIMESTAMP) > 600 --10 minutes

and s.spid not in (select distinct s.spid

  from master..sysprocesses as s with(NOLOCK)

  where s.loginame = 'domain\username') -- I use this to exclude a specific user and any multiple threads for the same spid where loginame isnt provided.

2 Comments
Level 12

Without the query text, there is no way to know which query for a SPID is running long. I can see why trying to include the query text would be a problem, but you could include the handle (r.sql_handle) instead.

What is long for one query is not long for another. Perhaps you could take the approach from the long-running agent jobs query and base the threshold on, if I recall correctly, 2 standard deviations longer than the average for the sql_handle. Of course, that won't help for non-parameterized queries.

There is also a hard-coded value for the loginame value to exclude, which I think would also make it impossible to make a standard part of the product as is. But I can't speak for SolarWinds, I'm just a customer.


- Mark

Community Manager
Community Manager
Status changed to: Open for Voting