SSRS Performance Tuning

The below are the key points needs to consider for better reporting performance .

We can identify sluggish reports (slow running ) from query execution log view from Report Server db.

From execution log view we can identify where the report server spending most of time i.e data retrieval , report processing and rendering .

Data Retrieval Time :

1. Some of the Reports performance significantly improved by querying from OLAP Cube .

2. T SQL Query used in the report needs to be tuned and optimized for better performance.

3. If real time data is not desired then create snapshots.

4. Queries bringing large data set shouldn't be run for interactive report viewing but should be derived via subscriptions .

5. Don't retrieve unnecessary data.

6. Increase query or report timeout.

High Report Processing Time :

1. Avoid sorting and grouping of the Reports .

2. Data which is grouped and sorted from the SQL db shouldn't be grouped and sorted at the report layer .

3. Add page breaks for large reports to take advantage of on-demand report processing introduced in SQL 2008 .

4. Avoid using Globals!Totalpages function in any of the expression in the reports.

5. Avoid images and charts nested inside tablix .

6. Use Drill Through Reports i.e Summary Report --> Detail Report whereever feasible since it reduces the data set significantly .

7. Avoid blank pages .

RS Scaleout Deployment :

  For Large reports avoid running them interactively and schedule them as email subscriptions which can be run on separate server dedicated for processing subscription jobs .

Reduce N/W Latency :

1. Interactive Reportserver and B2B Production can moved to a single server to reduce the n/w latency .

2. Ensure the single server is much higher configuration since both report service and SQL Server are resource intensive application.

3. Max server memory should be capped to limit SQL Server memory to 40-50% of the total physical RAM .


I hope the article was useful! and I need to let you know that this won’t stop here .keep in touch and feel free to ask any doubts related to this topic .