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

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!

  • 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
    

  • Thank you Gita!

    For most instances we have the alarm triggering (or not triggering) at the appropriate defined margins.

    Our issue comes when Spotlight regards a SPID as running for hours when it's only been running for minutes.  I'll see if support can dig something up.

  • I have identified 2 problems on previous versions of SoSSE that may relate to our issue.  KBs 230863 & 230755.  I need to perform a little more research on our end to confirm if these match.  Will open a new item with support after that.

    We do have an update pending & that may resolve, as well perhaps.