4 Replies Latest reply on Dec 8, 2016 2:15 PM by wlipford

    Odd Query behavoir - long execution and tempdb fills

    wlipford

      Posted in General DB but got no responses so I will try here.

       

      The query is basically pulling all of our custom poller stats for customer reporting requirements.

       

       

      Here is the query:

       

       

      SELECT DATEADD (hour, DATEDIFF (hour, 0, CustomPollerStatistics.DateTime), 0)

                AS DateTime,

             MIN (CustomPollerStatistics.MinRate) AS MinRate,

             AVG (CustomPollerStatistics.AvgRate) AS AvgRate,

             MAX (CustomPollerStatistics.MaxRate) AS MaxRate,

             SUM (CustomPollerStatistics.Total) AS Total,

             MIN (CustomPollerStatistics.RawStatus) as RawStatus,

             MIN (CustomPollerStatistics.Status) as Status,

       

      CustomPollerAssignment.NodeID,

      CustomPollerAssignment.InterfaceID,

             CustomPollers.UniqueName,

             CustomPollers.Description

        FROM (SolarWindsOrion.dbo.CustomPollerAssignment CustomPollerAssignment

              INNER JOIN SolarWindsOrion.dbo.CustomPollers CustomPollers

                 ON (CustomPollerAssignment.CustomPollerID =

      CustomPollers.CustomPollerID))

             INNER JOIN SolarWindsOrion.dbo.CustomPollerStatistics CustomPollerStatistics

                ON (CustomPollerStatistics.CustomPollerAssignmentID =

      CustomPollerAssignment.CustomPollerAssignmentID)

      WHERE     (DATEADD (hour,

      DATEDIFF (hour, 0, CustomPollerStatistics.DateTime),

      0) >= GETDATE () - 30)

             AND CustomPollers.UniqueName IN

      ('gprsAttached',

      'gprsNetworkTechnology',

      'gprsRegistered',

      'gprsSignalStrength')

      GROUP BY CustomPollerAssignment.NodeID,

               CustomPollerAssignment.InterfaceID,

      CustomPollers.UniqueName,

      CustomPollers.Description,

               DATEADD (hour,

      DATEDIFF (hour, 0, CustomPollerStatistics.DateTime),

      0)

        • Re: Odd Query behavoir - long execution and tempdb fills
          KMSigma

          I can't speak to performance because I have no data in the CustomPollerAssignment table, but I did refactor the query a little bit.

           

          SELECT DATEADD (hour, DATEDIFF (hour, 0, CustomPollerStatistics.DateTime), 0)
                    AS [DateTime],
                 MIN (CustomPollerStatistics.MinRate) AS MinRate,
                 AVG (CustomPollerStatistics.AvgRate) AS AvgRate,
                 MAX (CustomPollerStatistics.MaxRate) AS MaxRate,
                 SUM (CustomPollerStatistics.Total) AS Total,
                 MIN (CustomPollerStatistics.RawStatus) as RawStatus,
                 MIN (CustomPollerStatistics.Status) as Status,
                 CustomPollerAssignment.NodeID,
                 CustomPollerAssignment.InterfaceID,
                 CustomPollers.UniqueName,
                 CustomPollers.Description
          FROM CustomPollerAssignment 
              INNER JOIN CustomPollers ON CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID
              INNER JOIN CustomPollerStatistics ON CustomPollerStatistics.CustomPollerAssignmentID = CustomPollerAssignment.CustomPollerAssignmentID
          WHERE     (DATEADD (hour, DATEDIFF (hour, 0, CustomPollerStatistics.DateTime), 0) >= GETDATE () - 30)
              AND CustomPollers.UniqueName IN
                                           ('gprsAttached',
                                            'gprsNetworkTechnology',
                                            'gprsRegistered',
                                            'gprsSignalStrength')
          GROUP BY CustomPollerAssignment.NodeID,
                   CustomPollerAssignment.InterfaceID,
                   CustomPollers.UniqueName,
                   CustomPollers.Description,
                   DATEADD (hour, DATEDIFF (hour, 0, CustomPollerStatistics.DateTime), 0)
          

           

          You probably want to try and run this with a the execution plan estimated or shown.  This will help determine where the bottleneck is in the SQL call.

           

          What is the purpose of this query?  What data is it supposed to return and how is it supposed to be summarized?

          1 of 1 people found this helpful
            • Re: Odd Query behavoir - long execution and tempdb fills
              wlipford

              Hi and thanks for the reply.

               

              This is an external integration to a Customer portal where we are providing signal strength reports using our BI tool. We are going to have quite a bit more queries coming down the pipeline regarding this integration, we are eventually going to stand up a dedicated reporting instance but its schema will be mirrored so same slow exec times, are going to come into play.

               

              I ran the query thru the estimated execution plan in SQL Server Manager. It appears the bottle neck to be the "Sort" with the cost being 79% (attached screen shot). SQLBottleNeck_SignalStrength.png

               

              The next highest bottle neck based on "cost" is Clustered Index Seek on the primary key for CustomPollerStatistics_Detail but that is only 6%.

               

              Next highest is 3% cost which is the Gather Streams, which is expected. All others are super low and are also index search.