Reporting Services Performance Optimization

Generic guidelines

- All SQL performance best practices are applicable.

- Optimize Report Data Set queries.

- Don’t retrieve more data than needed. Client side filtering is expensive. Use server side filtering or grouping when possible.

- For large complex reports consider breaking them up into smaller chunks.

- 64 bit is advisable in a scale-up environment. If on 32 bit consider the /3GB switch

- If Performance is slow after period of inactivity disable the idle timeout in AppPool performance tab.

- On Windows 2003 you can set “Maximum number of Worker Processes” in IIS for the App Pool.

- Check the Application Restarts counter in ASP.Net counters. There have been issues in the past with Antivirus and ASP.NET App Doman restarts tab.

- Create Report Server catalog on multiple Files in SQL.

- In Scale out, have a separate server for Ad hoc reporting such as ReportBuilder to increase predictability.

- Where feasible, work against a “Reporting” database rather than an OLTP database for large reports and specially for ADHOC reporting.

- And of course Benchmark and stress test before production.