2 Replies Latest reply on Aug 18, 2016 10:28 AM by lcsw2013

    Long running queries.

    lcsw2013

      Hi,

       

      I have an issue in Swis V3, where I'm seeing a large amount of long running queries bogging down the system. And analyzing these queries I'm having a hard time trying to pin point what exactly it's trying to do. I'm trying to figure this out so I can optimize the queries or  delete them all together. I'll post what I'm seeing below and hoping I can get some guidance from the community on this.

       

      WARN  SolarWinds.InformationService.Core.InformationService - (null)  Support! -- LONG RUNNING QUERY: Session uuid:ac3d87cd-c558-43dd-bc2b-3b9f16c9fda2;id=53 - Query took 16098.5107 ms:  SELECT DISTINCT OrionSite.ID AS OrionSiteID, OrionSite.Name AS SiteName, OrionSite.SiteID as InstanceSiteID,

                                       Data.AlertActiveID, Data.AlertObjectID, Data.Name,

                                      Data.AlertMessage, Data.Severity, Data.ObjectType,

                                      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.AlertActiveID, AlertObjects.AlertObjectID, AlertConfigurations.Name,

                                      AlertConfigurations.AlertMessage, AlertConfigurations.Severity, AlertConfigurations.ObjectType,

                                      AlertObjects.EntityUri, AlertObjects.EntityType, AlertObjects.EntityCaption, AlertObjects.EntityDetailsUrl,

                                      AlertObjects.RelatedNodeUri, AlertObjects.RelatedNodeDetailsUrl, AlertObjects.RelatedNodeCaption, AlertObjects.AlertID,

                                      AlertActive.TriggeredDateTime, AlertObjects.LastTriggeredDateTime, AlertActive.TriggeredMessage AS Message, AlertActive.AcknowledgedBy AS AccountID,

                                      AlertActive.LastExecutedEscalationLevel, AlertActive.AcknowledgedDateTime, AlertActive.Acknowledged, AlertActive.AcknowledgedBy, AlertActive.NumberOfNotes,

                                      AlertObjects.TriggeredCount, AlertObjects.AlertNote as AcknowledgedNote, 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 RETURN XML RAW

       

      I see multiple from the above queries happening together. Killing my database with multiple large requests at once.

       

      I see an even larger amount of lines like the one below:

       

      WARN  SolarWinds.InformationService.Core.InformationService - (null)  Support! -- LONG RUNNING QUERY: Session uuid:ac3d87cd-c558-43dd-bc2b-3b9f16c9fda2;id=5 - Query took 98389.1151 ms: SELECT EntityType, Name, Prefix, KeyProperty, KeyPropertyIndex FROM Orion.NetObjectTypes (nolock=true) RETURN XML RAW

       

      And even more from the line below:

       

      [EasyNetQ consumer dispatch thread] WARN  SolarWinds.InformationService.ChangeBroker.Broker - (null)  Change indication for Orion.Packages.Wireless.AccessPoints is required to contain {ID} key properties, but {ID} missing.

       

      What are these things pointing too? Has anyone seen anything similar? Let me know so I can optimize my environment and make the swis v3 stop hanging up my site.

       

      Thank you!