Microsoft SQL Server Reporting Services - measuring and improving performance

§ Optimize your report queries. Usually the bulk of report execution time is spent executing queries and retrieving results. If you are using SQL Server, tools such as Query Analyzer and Profiler can help you optimize your queries and Database Tuning Advisor can suggest better indexes for your database.

§ Measure: The Report Server ExecutionLog table contains data on reports performance. The following query can give you a quick look at how long it took to execute certain reports, and where the bulk of the time was spent. TimeDataRetrieval contains the number of ms. spent getting data from the report's data source(s).

use ReportServer

Select * from ExecutionLog with (nolock) order by TimeStart DESC

Make sure to include the “nolock” hint. The ExecutionLog table is used by the RS runtime and locking it can severely degrade your server’s performance.

A better solution is to run the DTS package that comes with Reporting Services. It will move data out of the runtime tables into a set of reporting tables. That way you are minimizing the amount of interference with the RS runtime. You can also take advantage of the built-in reports based on the execution logs, and/or build your own reports.

§ If you don’t need data in your report, don’t retrieve it. Levering database operations such as filtering, grouping, and aggregates can reduce the amount of data that is processed in the report, improving performance.

§ Keep your reports modest in size and complexity. Do users really want to look at a 1,000 page report?

§ If performance is extremely bad even for single users, check the Application Restarts counter in the ASP.NET category; some antivirus software is known to “touch” configuration files, thus causing expansive Application Domain restarts in the report server web service. For more information, search https://support.microsoft.com/ for articles relating to “antivirus and ASP.NET”.

§ If performance is slow on the first web service access after there have not been any accesses for a certain time period, disable the idle timeout on the Performance tab in the Application Pool in IIS Manager.

§ Execute reports from cached / snapshot data as opposed to live whenever possible.

§ Limit non-essential background processing to off-peak hours in order to avoid contention with on-line users.

§ If you load your report server up with 4GB memory, remember to set the /3GB switch in C:\boot.ini so application processes can make use of it.

§ If a single server can’t handle the workload, consider locating the Reporting Services catalog on a remote SQL Server instance as your first step toward increasing system capacity.

§ If one report server configured with a remote catalog still doesn’t adequately support your workload, consider increasing the available resources on the system hosting your report server (scale-up) or setting up a clustered web farm of report servers (scale-out).