SSRS Report 성능 모니터링

 

SSRS Report Performance monitoring

 

Reporting Service 보고서를 관리하면서 사용자의 보고서 사용 및 보고서 문제를 모니터링 해야하는 경우가 발생합니다.

그럴 경우 ReportServer 데이터베이스에서 확인을 할 수 있습니다.

 

최근 실행된 보고서 Top 20

  SELECT TOP 20
     C.Path, C.Name,
     EL.UserName,
     EL.Status,
     EL.TimeStart,
     EL.[RowCount],
     EL.ByteCount,
     (EL.TimeDataRetrieval
          + EL.TimeProcessing
          + EL.TimeRendering)/1000 AS TotalSeconds,
     EL.TimeDataRetrieval,
     EL.TimeProcessing,
     EL.TimeRendering
  FROM ExecutionLog EL
     INNER JOIN Catalog C ON EL.ReportID = C.ItemID
  ORDER BY TimeStart DESC

 

최근 30일 동안 가장 느린 보고서 Top 10

  SELECT TOP 10
     C.Path, C.Name,
     Count(*) AS ReportsRun,
     AVG((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS AverageProcessingTime,
     Max((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS MaximumProcessingTime,
     Min((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS MinimumProcessingTime
  FROM ExecutionLog EL
     INNER JOIN Catalog C ON EL.ReportID = C.ItemID
  WHERE EL.TimeStart>Datediff(d,GetDate(),-30)
  GROUP BY C.Path,C.Name
  ORDER BY
     AVG((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) DESC

 

최근 30일 동안 보고서를 많이 본 사용자 Top 10

  SELECT TOP 10
     EL.UserName,
     Count(*) AS ReportsRun,
     Count(DISTINCT C.[Path]) AS DistinctReportsRun
  FROM ExecutionLog EL
     INNER JOIN Catalog C ON EL.ReportID = C.ItemID
  WHERE EL.TimeStart>Datediff(d,GetDate(),-30)
  GROUP BY EL.UserName
  ORDER BY Count(*) DESC

 

최근 30일 동안 가장 많이 본 보고서 Top 10

  SELECT TOP 10
     C.Path, C.Name,
     Count(*) AS ReportsRun,
     AVG((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS AverageProcessingTime,
     Max((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS MaximumProcessingTime,
     Min((EL.TimeDataRetrieval
        + EL.TimeProcessing
        + EL.TimeRendering)) AS MinimumProcessingTime
  FROM ExecutionLog EL
     INNER JOIN Catalog C ON EL.ReportID = C.ItemID
  WHERE EL.TimeStart>Datediff(d,GetDate(),-30)
  GROUP BY C.Path, C.Name
  ORDER BY Count(*) DESC

 

 

실패한 보고서 Top 20

  SELECT TOP 20
     C.Path, C.Name,
     EL.UserName,
     EL.Status,
     EL.TimeStart,
     EL.[RowCount],
     EL.ByteCount,
     (EL.TimeDataRetrieval
          + EL.TimeProcessing
          + EL.TimeRendering)/1000 AS TotalSeconds,
     EL.TimeDataRetrieval,
     EL.TimeProcessing,
     EL.TimeRendering
  FROM ExecutionLog EL
     INNER JOIN Catalog C ON EL.ReportID = C.ItemID
  WHERE EL.Status <> 'rsSuccess'
  ORDER BY TimeStart DESC

 

원본 위치 <http://www.purplefrogsystems.com/blog/?p=13>