– 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.