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!]
Very good post!!!!!!!
Cheers!!
thanks very much – just what I needed
This is very useful, thanks 😉
Well, great, this is gonna help me tomorrow in pinpointing some crashing and bad performing reports in my audit. THX!
Thank you very much, very helpful post
A very useful post. Thanks a ton!