With Microsoft SQL Server Reporting Services you can build very nice and useful reports. However, reports which do not perform will not be used by the users. In my blog,
Tips to improve performance of MS Reporting service reports, I gave some performance tips when building SSRS reports. After deploying your reports it is always interesting to know how your reports are used. After a while, you want to know some performance statistics of your deployed SSRS reports. This can be useful information to start analyzing the performance of the reports. In this blogpost, I will give some queries which you can use to get SSRS statistics of your reports. These statistics are stored in the table: ExecutionLog of your the ReportServer database. This table contains statistics of the last 60 days. More information about the ExecutionLog table can be found
here.
Some examples of statistics which can be retrieved from the ExecutionLog table:
- Most active users
- Most popular reports
- Reports with most DataRetreival in Total
- Reports with highest execution time in Total
- Reports with highest data retrieval in Total
- Slowest reports
Of course, you can use these queries as datasets in your own monitoring SSRS reports.
-- Most Active users.
SELECT Username, COUNT(*) as Executed
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY UserName
ORDER BY COUNT(*) DESC
-- Most popular reports
SELECT Name,b.path, COUNT(*)AS Executed
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name,b.path
ORDER BY COUNT(*) DESC
-- Reports with most Dataretrieval
SELECT TOP 25 Name, b.path, AVG(BYTECOUNT/(1024)) AS AVG_KB,
SUM(BYTECOUNT/(1024)) as SUM_KB, COUNT(*)AS Executed
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name,b.path
ORDER BY SUM_KB desc
-- Reports with most execution time
SELECT TOP 25 Name, b.path,
SUM(TimedataRetrieval + Timeprocessing + TimeRendering)/1000 AS ExecutionTotalTimeinSec,
COUNT(*) AS Executed,
AVG(TimedataRetrieval + Timeprocessing + TimeRendering) AS AVGExecutionTimeinSec
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name, b.path
ORDER BY ExecutionTotalTimeinSec desc
-- Reports with most Rendering time
SELECT TOP 25 Name,b.path,
SUM(TimeRendering)/1000 AS RenderingTotalinSec,
COUNT(*) as Executed,
AVG(TimeRendering) AS AvgRenderinginMs
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name,b.path
ORDER BY RenderingTotalinSec desc
-- Reports with most Data retrieval time
SELECT TOP 25 Name,b.path,
SUM(TimeDataRetrieval)/1000 AS DataRetrievalTimeinSec,
COUNT(*), AVG(TimeDataRetrieval) AS AvgTimeDataRetrievalinMs
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name,b.path
ORDER BY AvgTimeDataRetrievalinMs desc
-- Slowest executed report.
SELECT TOP 10 Name,b.path, Parameters, FORMAT,TimeStart,
TimeEnd, ByteCount,
(TimedataRetrieval + Timeprocessing + TimeRendering)/1000 AS TotalTimeinSec
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
ORDER BY (TimedataRetrieval + Timeprocessing + TimeRendering) DESC
Photo credit: Ewan traveler
