Apparently we're getting some Long Running SQL alarms that aren't valid.

Question

Hi, 
 The last time I researched this, the recommended solution was to update. Which we did. Then the problem trailed off. Recently they started up again. 
 
 Basically, the alarms state that the SQL has been running for X amount of time, but it's incorrect. The SPID will only have been running for a much shorter duration. We end up wasting time on researching things that aren't actually problems. 
 Anyone else have to deal with this? Anyone have a solution? 
 
 Thanks!

Gita Sharifi · Answer

Hi Bill, By default, the Long Running SQL alarm threshold is at 60 seconds and has a normal level severity. Adding additional thresholds will raise the alarm which I assume you've configured. Spotlight obtains the alarm value from sys.dm_exec_requests.total_elapsed_time (ms) column. When the alarm is triggered you can run the attached script to obtain the data Spotlight uses for this alarm. To help diagnose the alarm, you can enable Request Elapsed Time and Request Start Time columns of the Sessions dashboard. The Sessions dashboard data should reflect what the script returns when diagnosing the alarm in real time. I haven't see any issues regarding this alarm in the latest version of Spotlight, 13.3. Hope that helps out. I suggest to contact our Support team for more help if needed. Best, Gita Spotlight Support QS_LongRunningSQL.sql /* -- SoSSE 35.0.0.1550 -- File - Procedure\sqlserver_spotlight\2005\QS_LongRunningSQL.sql -- * -- * Copyright 2020 Quest Software Inc. ALL RIGHTS RESERVED. -- * */ use master declare @isServiceBroker char; declare @queryText nvarchar(4000); declare @sessionID int; declare @sql nvarchar(100); set @isServiceBroker = 'N'; if isnull(object_id(N'tempdb..#Quest_longRunningSQL'),0) <> 0 drop table #Quest_longRunningSQL if isnull(object_id(N'tempdb..#Quest_sql'),0) <> 0 drop table #Quest_sql create table #Quest_sql ( EventType text, Parameters int, sqltext text ) select convert(nvarchar, r.session_id) + '.' + convert(nvarchar, r.request_id) as session_request_id, r.session_id, left(case when r.statement_start_offset > 0 then case when r.statement_end_offset <= 0 then substring(qt.text, (r.statement_start_offset/2) + 1, 2147483647) else substring(qt.text, (r.statement_start_offset/2) + 1, (abs(r.statement_end_offset - r.statement_start_offset))/2 + 1) end else case when r.statement_end_offset <= 0 then rtrim(ltrim(qt.text)) else left(qt.text, (r.statement_end_offset/2) +1) end end, 10240) as query_text, r.cpu_time, r.total_elapsed_time, db_name(r.database_id) as databasename, rtrim(s.loginame) as loginname, ses.program_name as programname, ses.host_name as hostname, r.status as status into #Quest_longRunningSQL from sys.dm_exec_requests r join master.dbo.sysprocesses s on r.session_id = s.spid left outer join sys.dm_exec_sessions ses on r.session_id = ses.session_id outer apply sys.dm_exec_sql_text(r.sql_handle) as qt where not exists (select top (1) 1 from sys.dm_exec_sessions s where s.session_id = r.session_id and host_process_id is null) and r.session_id <> @@spid and ((lower(qt.text) not like N'%openrowset(trcdata%' and lower(qt.text) not like N'%openrowset(table trcdata%') or qt.text is null) and (@isServiceBroker = (case when upper(r.wait_type) in (N'BROKER_CONNECTION_RECEIVE_TASK' , N'BROKER_RECEIVE_WAITFOR', N'BROKER_DISPATCHER' , N'BROKER_ENDPOINT_STATE_MUTEX' , N'BROKER_FORWARDER' , N'BROKER_INIT' , N'BROKER_MASTERSTART' , N'BROKER_REGISTERALLENDPOINTS' , N'BROKER_SERVICE' , N'BROKER_SHUTDOWN' , N'BROKER_TASK_SUBMIT' , N'BROKER_TRANSMISSION_OBJECT' , N'BROKER_TRANSMISSION_TABLE' , N'BROKER_TRANSMISSION_WORK') then 'Y' else 'N' end) or (lower(qt.text) not like N'%waitfor%')) declare QueryText_Cursor cursor for select session_id,query_text from #Quest_longRunningSQL open QueryText_Cursor; fetch next from QueryText_Cursor into @sessionID, @queryText; while @@FETCH_STATUS = 0 begin if @queryText is null begin set @sql = N'dbcc inputbuffer ('+convert(nvarchar(20),@sessionID)+')' insert into #Quest_sql (EventType, Parameters, sqltext) exec (@sql) select @queryText=sqltext from #Quest_sql truncate table #Quest_sql if @queryText is not null update #Quest_longRunningSQL set query_text = @queryText where session_id = @sessionID end fetch next from QueryText_Cursor into @sessionID , @queryText; end; close QueryText_Cursor; deallocate QueryText_Cursor; select * from #Quest_longRunningSQL order by session_id drop table #Quest_longRunningSQL drop table #Quest_sql

Apparently we're getting some Long Running SQL alarms that aren't valid.

Top Replies