Part 1. Know your report’s performance
SQL Server Reporting Services (SSRS) is a powerful report building and deployment tool that is frequently used by a large number of institutions. However, a report that needs a lot of time to be rendered is useless as users lose patience and interest quickly when they access reports with performance issues.
So, given that, it’s important to consider the performance statistics of your reports, understand them, and know how to use them to improve their performance. In today’s post, we will talk about how to know your reports by accessing their running statistics.
Reporting does not end right after a report is deployed. It is always interesting and useful to know how your reports are used, by whom and how often. Also, when you know running statistics of your deployed SSRS reports, you know where to start from to keep your report in good shape.
All reports running statistics can be accessed by querying the Executionlog in the report server database. More information about the ExecutionLog table can be found here. One thing to keep in mind is that the Executionlog table only keeps the report’s statistics of the last 60 days.
Here are some simple examples of statistics which could be used to help you understand your reports better and all these statistics could be retrieved from the ExecutionLog table in report server database:
- Who are most active users to the reports system?
- What are the most popular reports, and the most useful?
- What are slowest reports, which potentially can have the ones that need the performance improved?
You are welcomed to use these following queries in your own report sever database to monitor your SSRS reports.
— Most Active users.
SELECT Top 20 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
— performance statics of running Reports
SELECT Name, b.path,
COUNT(*) AS Executed,
AVG(TimedataRetrieval + Timeprocessing + TimeRendering)/1000 AS AVGExecutionTimeinSec,
AVG(TimedataRetrieval)/1000 AS AVGDataRetrievalTimeinSec,
AVG(Timeprocessing)/1000 AS AVGProcessingTimeinSec,
AVG(TimeRendering)/1000 AS AVGRenderinginSec,
AVG(BYTECOUNT/(1024)) AS AVG_KB
FROM ExecutionLog EL
JOIN CATALOG b ON EL.reportid = b.itemid
GROUP BY Name, b.path
HAVING AVG(TimedataRetrieval + Timeprocessing + TimeRendering)/1000 >1
ORDER BY AVGExecutionTimeinSec desc