6 Replies Latest reply on Jul 27, 2009 12:24 PM by lchance

    Netflow App report uses 100% CPU

    tao_lao

      hi All,

      I have setup a SQL 2005 reporting service and i would like to run a report for top 10 app per node per month. I have a query working but It takes 3 minutes to finish and CPU( 8 cores)  hits almost 100% during that period. the query as below, could any help to customize the query? appreciate.

      SELECT
      CONVERT(DateTime,
      LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
      101) AS SummaryMonth,
      Nodes.Caption AS NodeName,
      NetflowApplicationSummary.AppName AS Application_Name,
      NetflowApplicationSummary.Port AS Port_Number,
      ROUND(SUM(NetflowApplicationSummary.TotalBytes)/1000000000,2) AS SUM_of_Bytes_Transferred

      FROM
      NetflowApplicationSummary INNER JOIN Nodes ON (NetflowApplicationSummary.NodeID = Nodes.NodeID)

      WHERE  NetflowApplicationSummary.AppName IN

      (SELECT TOP 10

      NetflowApplicationSummary.AppName AS Application_Name


      FROM
      NetflowApplicationSummary INNER JOIN Nodes ON (NetflowApplicationSummary.NodeID = Nodes.NodeID)


      WHERE
      ( DateTime BETWEEN
       CONVERT(DateTime,LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),101)

      AND
      DateAdd("month",1, CONVERT(DateTime,LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),101) )
       )
       AND 
      (
        (Nodes.Caption LIKE '%RouterA%')
      )


      GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
       NetflowApplicationSummary.AppName


      ORDER BY  CONVERT(DateTime,LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),101)  ASC, SUM(NetflowApplicationSummary.TotalBytes) DESC
      )

      AND
      ( DateTime BETWEEN 39857 AND 39947 )
       AND 
      (
        (Nodes.Caption LIKE '%HTRAND%')
      )


      GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
      Nodes.Caption, NetflowApplicationSummary.AppName, NetflowApplicationSummary.Port


      ORDER BY SummaryMonth ASC, SUM_of_Bytes_Transferred