Let us not get into a discussion of why we are still talking about SSRS here and not some other fancy BI technologies like Power BI, Tableau, etc. I like SSRS for reasons like delivering crisp, paginated reports to end users which then, they can easily download in a format they want and go about analyzing their data with a tool they prefer. There are other tools I prefer to visualize data in many different ways or if I need to slice and dice the data by many dimensions. But here I am going to talk about the most common issue in any report developer’s life using SSRS “Report takes too long and still renders blank screen”.
Every now and then we will hear business users complaining about reports running slow or at times not even rendering and just showing a blank screen. So for cases like these, where should you start?
Start with the ExecutionLog3 view in Report Server
All the report execution information is stored in the report server database which by default is named ReportServer. The views in the ReportServer database provide data in a friendlier way than browsing each internal table. Always use the ExecutionLog3 view compared to older views (ExecutionLog/ExecutionLog2) as they are just there for backward compatibility. Take your time to understand all of the columns returned by the view – ExecutionLog3. Read here for more.
The below query will return all records from the view for a particular report that you want to troubleshoot.
USE ReportServer
SELECT *
FROM ExecutionLog3
WHERE
ItemPath = '/reportpath/reportname'
ORDER BY
TimeStart DESC
My focus will turn into three columns (Of course understanding other columns are equally important too). TimeDataRetrieval, TimeProcessing and TimeRendering
- TimeDataRetrieval: Number of milliseconds spent retrieving the data.
If the value is too high, then you know your data is taking too long to be retrieved from the data source. Open the report query and start asking questions.- Never pull more data than needed. Head over to another post to learn a few tricks about performance tuning.
- Avoid having warehouse and reporting services in the same server to avoid memory contention (yeah, yeah I know having them on separate servers can potentially add extra load to retrieve data across the network).
- TimeProcessing: Number of milliseconds spent processing the report.
A higher value tells you the report has a lot of things to do after the data is retrieved. It is affected by different expressions used in the report, paging, and the layout itself.- Do not use any expressions unless the report demands it. The most commonly used expression is [&TotalPages], imagine a report processor having to paginate a report that has 1000 pages before even the first page can be rendered. Every expression is calculated whether hidden or not.
- Use page breaks. If no page breaks are used, the entire report must be processed before a user can see the first page.
- Do not use complex aggregation in your data regions. If you are displaying just the detailed data using a tablix, you do not need to group every column inside the tablix.
- Let the database engine help you. Do aggregation and sorting in your report query to minimize processing times.
- Pay special attention to the ‘KeepTogether” property on tablix members as it requires additional processing time.
- Double-check the text box properties for a report that has too many text boxes as properties such TextAligh, CanGrow, and CanShrink could add more to processing times.
- Use Subreports very carefully inside a tablix. It works fine with minimal subreports but consider using drill-through reports.
- TimeRendering: Number of milliseconds spent rendering the report.
This column will tell you if the report is taking too long to render. The report processor after the data is retrieved will combine the layout and the data in the interim format and passes it to the rendering extension. Multiple factors such as data volumes, pages, etc. impact the rendering time. Please note while exporting a report to a certain format your report must be optimized for that renderer else the time to render will shoot high.- Report performance varies significantly based on chosen rendering format. CSV, XML, and HTML require the least amount of memory and thus are the quickest as well. PDF and Excel require more CPU and RAM.
Do not forget to open the XML from the ‘AdditionalInfo‘ column as it contains lots of good information as well like what processing engine is used, memory usage, etc.
