Purple Frog Systems

Today’s Frog-Blog top-tips are quite simple ones, but ones that I always find very useful. SSRS Report Performance monitoring.

Once you start to build up a few Reporting Services reports, you need to find out how they’re performing, whether any particular reports are causing the server problems and even which users are running which reports. The following set of queries should point you in the right direction. They should all be run against the ReportServer database.

The 20 most recently run reports

 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 

The slowest reports (in the last 28 days)

 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(),-28)
 GROUP BY C.Path,C.Name
 ORDER BY AVG((EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering)) DESC 

The most active users

 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(),-28)
  GROUP BY EL.UserName
  ORDER BY Count(*) DESC 

The most popular reports

 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(),-28)
  GROUP BY C.Path, C.Name
  ORDER BY Count(*) DESC 

Failed Reports

 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 

There are countless other variations, but this should be enough to get you going.

[Update: 05/09/2011 - Thanks to Jonathan [Twitter|Website] for pointing out a typo!]