4 Replies Latest reply on Sep 3, 2015 7:41 PM by leonisof

    Report generating large amount of temp

    leonisof

      -----------------------------------------------------------------

      EDIT: I originally posted this in the NTA forums, but I think I've found that these queries are being generated through the Orion Report Writer, so I've moved it to the Reports forum.

      -----------------------------------------------------------------

       

      Hi,

      I recently inherited a NetFlow system, so apologies in advance if I ask things that can be found in the documentation or I've posted in the wrong section.

       

      We recently found that there were monthly reports being run on the NetFlow database (on SQL Server 2008 R2) that would absolutely hammer through the TempDB, the reports would just continually chew up all the TempDB on the disk until there was none (disk was around 500GB).

       

      I found a similar submission here on thwack with almost the same query that we use, so I'm assuming the query is part of a template or a package available somewhere? Here's the query from one of the reports that we use:

       

      SELECT  TOP 50 FlowCorrelation.FullHostname AS Full_Hostname,

      FlowCorrelation.IPAddress AS IP_Address,

      Nodes.Caption AS NodeName,

      SUM(NetflowEndpointsSummary.TotalBytesDst) AS SUM_of_Bytes_Transferred_Rx,

      SUM(NetflowEndpointsSummary.TotalBytesSrc) AS SUM_of_Bytes_Transferred_Tx,

      SUM(NetflowEndpointsSummary.TotalBytes) AS SUM_of_Bytes_Transferred_Total,

      Nodes.NodeID AS NodeID

      FROM

      (NetflowEndpointsSummary LEFT OUTER JOIN FlowCorrelation ON (NetflowEndpointsSummary.IPSort = FlowCorrelation.IPAddressSort)) 

      INNER JOIN Nodes ON (NetflowEndpointsSummary.NodeID = Nodes.NodeID)

      WHERE

      ( DateTime BETWEEN 41553 AND 41584 )

      AND

      (

        (FlowCorrelation.FullHostname LIKE '%mycingular.net%')

      )

      AND

      (

      (EXISTS(SELECT 1 FROM NetFlowSources WITH(nolock) WHERE NetFlowSources.InterfaceID=InterfaceID AND NetFlowSources.Enabled=1))

      )

      GROUP BY FlowCorrelation.FullHostname, FlowCorrelation.IPAddress, Nodes.Caption, Nodes.NodeID

      ORDER BY 6 DESC

       

      Also investigating the database side of things, but I posted here just in case anyone had encountered the same issue as well. Thanks