Long running reports

WinterSoldier

Hi,

I saw multiple warning messages information service log stating long running queries which is taking longer than usual to execute and impacting website.

WARN  SolarWinds.InformationService.Core.InformationService - (null) (null)	 Support! -- LONG RUNNING QUERY: OperationContextId 60b6e459-4465-4a7e-94d2-a7be0ef6f77b - Query took 168267.4412 ms:  SELECT DISTINCT OrionSite.SiteID, OrionSite.Name AS SiteName,
                                 Data.AlertActiveID, Data.AlertObjectID, Data.Name,
                                Data.AlertConfigurationMessage, Data.Severity, Data.ObjectType, Data.ObjectTriggeredThisAlertDisplayName,
                                Data.EntityUri, Data.EntityType, Data.EntityCaption, Data.EntityDetailsUrl,
                                Data.RelatedNodeUri, Data.RelatedNodeDetailsUrl, Data.RelatedNodeCaption, Data.AlertID, 
                                Data.TriggeredDateTime, Data.LastTriggeredDateTime, Data.Message, Data.AccountID, 
                                Data.LastExecutedEscalationLevel, Data.AcknowledgedDateTime, Data.Acknowledged, Data.AcknowledgedBy, Data.NumberOfNotes, 
                                Data.TriggeredCount, Data.AcknowledgedNote, Data.Canned, Data.Category ,
                                '' AS IncidentNumber, '' AS IncidentUrl, '' AS AssignedTo
                                FROM (

                                SELECT AlertActive.InstanceSiteID, AlertActive.InstanceSiteID AS SiteID, AlertActive.AlertActiveID, AlertObjects.AlertObjectID, AlertObjects.AlertObjectID AS ActiveAlertID, AlertConfigurations.Name, AlertConfigurations.Name AS AlertName,
                                AlertConfigurations.AlertMessage AS AlertConfigurationMessage, AlertConfigurations.Severity, AlertConfigurations.Severity AS SeverityOrder, AlertConfigurations.ObjectType,
                                AlertObjects.EntityUri, AlertObjects.EntityUri AS TriggeringObjectEntityUri, AlertObjects.EntityType, AlertObjects.EntityType AS TriggeringObjectEntityName, AlertObjects.EntityCaption, AlertObjects.EntityDetailsUrl,
                                AlertObjects.RelatedNodeUri, AlertObjects.RelatedNodeUri AS RelatedNodeEntityUri, AlertObjects.RelatedNodeDetailsUrl, 
                                AlertObjects.RelatedNodeCaption, AlertObjects.RelatedNodeCaption AS RelatedNode, AlertObjects.AlertID, AlertObjects.AlertID AS AlertDefId, 
                                AlertActive.TriggeredDateTime, AlertActive.TriggeredDateTime AS TriggerTime,
                                SecondDiff(AlertActive.TriggeredDateTime, getUtcDate()) AS ActiveTimeDisplay, SecondDiff(AlertActive.TriggeredDateTime, getUtcDate()) AS ActiveTimeSort,
                                AlertObjects.LastTriggeredDateTime, AlertActive.TriggeredMessage AS Message,
                                AlertActive.TriggeredMessage AS AlertMessage, AlertActive.AcknowledgedBy AS AccountID, 
                                AlertActive.LastExecutedEscalationLevel, AlertActive.AcknowledgedDateTime, AlertActive.AcknowledgedDateTime AS AcknowledgeTime, AlertActive.Acknowledged, AlertActive.AcknowledgedBy, AlertActive.NumberOfNotes, CASE WHEN IsNull(AlertObjects.EntityCaption,'') <> '' AND IsNull(AlertObjects.RelatedNodeCaption, '') <> '' AND IsNull(AlertObjects.EntityType, '') <> 'Orion.Nodes'
                                                                        THEN CONCAT(AlertObjects.EntityCaption, ' on ', AlertObjects.RelatedNodeCaption) 
                                                                        ELSE CASE WHEN IsNull(AlertObjects.EntityCaption,'') <> '' THEN AlertObjects.EntityCaption ELSE AlertObjects.RelatedNodeCaption END
                                                                       END AS ObjectTriggeredThisAlertDisplayName,
                                AlertObjects.TriggeredCount, AlertObjects.TriggeredCount AS TriggerCount, AlertObjects.AlertNote as AcknowledgedNote, AlertObjects.AlertNote as Notes, AlertConfigurations.Canned, AlertConfigurations.Category 
                                FROM Orion.AlertObjects AlertObjects INNER JOIN Orion.AlertActive (nolock=true) AlertActive ON AlertObjects.AlertObjectID=AlertActive.AlertObjectID AND AlertObjects.InstanceSiteID=AlertActive.InstanceSiteID INNER JOIN Orion.AlertConfigurations (nolock=true) AlertConfigurations ON AlertConfigurations.AlertID=AlertObjects.AlertID AND AlertConfigurations.InstanceSiteID=AlertObjects.InstanceSiteID) AS Data LEFT JOIN Orion.Sites AS OrionSite ON OrionSite.SiteID=Data.InstanceSiteID WHERE 1=1 AND (1=1 AND (1=1) AND (1=1) AND (Severity IN (2,3,2,1))) ORDER BY [SeverityOrder] desc WITH ROWS 1 TO 5 WITH TOTALROWS RETURN XML RAW
2021-06-17 01:00:02,469 [209] WARN  SolarWinds.InformationService.Core.InformationService - (null) (null)	 Support! -- LONG RUNNING QUERY: OperationContextId f264d1e7-0d94-4a42-b47d-9c16c4c9233d - Query took 10951352.1627 ms: Verb Cortex.Orion.Node.Core.AddToCortex

Is anyone facing same?  I need to know which query or report is taking more time than usual to execute. If any one can help here. Please let me know if there is any SQL or SWQL to find out the long running reports or queries.

Thanks.

KMSigma.SWI

I used to get it when I was pulling datasets that were HUGE for a widget or something.  The SQL statement is listed in the error log you posted, so you can always run it manually and then look to see how long it takes to execute.

To 'find' the element that's running into this, you can reference Orion Platform Troubleshooting - Report (solarwinds.com) which has some good links.  If it turns out that this is an Out of the Box widget causing these issues, then I would definitely open a support case.  If it turns out to be a custom element (either a Custom Query Widget on a page or a custom report), then you should try to limit the number of returned rows or update the SQL/SWQL so it runs more efficiently.