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)

KMSigma.SWI

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.DescriptionFROM CustomPollerAssignment     INNER JOIN CustomPollers ON CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID    INNER JOIN CustomPollerStatistics ON CustomPollerStatistics.CustomPollerAssignmentID = CustomPollerAssignment.CustomPollerAssignmentIDWHERE     (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?