Troubleshooting Reporting Services Performance Issues

In the previous installment of our series dedicated to the most prominent features of SQL Server 2005 Express Edition, we have discussed logging and tracing options that could be helpful in resolving Reporting Services-related issues. In the course of our presentation, we pointed out that identifying a misconfigured or malfunctioning component might be challenging, due to the inherent complexity of the product, which is dependent on several distinct technologies. The goal of this article is to systematize the troubleshooting approach by focusing specifically on performance problems (as opposed to those impacting functionality).

As we have mentioned earlier, performance of Reporting Services in SQL Server 2005 Reporting Services is affected by a variety of factors, including the availability of operating system resources, efficient access to data store, state of Report Server user sessions, and Internet Information Services characteristics, such as cache optimization or configuration of ASP.NET application pools. Resulting delays might take place during the following stages of report generation:

• data retrieval
• report processing
• report rendering

Data retrieval involves extracting a dataset from data sources that a report is based on. In order to decrease its response time, take advantage of native SQL Server functionality that optimizes query performance (rather than applying the equivalent features within your reports). In particular, when generating a dataset, limit its size (effectively lowering amount of data returned to the Report Server) by applying the aggregation and filtering capabilities of the Query Processor (leveraging WHERE and HAVING clauses) and pre-sorting its content whenever applicable. (You can find more information about the specifics of this approach by referencing Improving Data Retrieval Performance topic in SQL Server 2005 Books Online). Make sure to evaluate the performance of your queries (while Profiler is not available in SQL Server 2005 Express Edition, you could substitute it with open source alternatives). Note that benefits from potential performance improvements offered by report snapshots are beyond your reach, since this feature requires a full-featured edition of SQL Server. On the other hand, keep in mind that the recommendation regarding placement of Report Server Web site and its databases on the same computer is actually enforced due to restrictions inherent to SQL Server 2005 Express Edition.

Report processing combines the records retrieved from a data source with a layout contained in the report definition. Its execution speed is dependent on a number and complexity of visual elements incorporated into the layout (such as tables, lists, matrices, headers, footers, or images) and any additional expressions that manipulate the existing dataset (such as data region grouping, aggregates, filtering, or sorting). For specific design recommendations that help improve performance during this stage, refer to SQL Server 2005 Books Online.

During rendering, an intermediate report format generated in the processing stage is subjected to additional changes that depend on a desired output type (Excel, PDF, or HTML) and destination (printing vs online viewing). This is primarily due to differences between what constitutes a page in each of these cases. The most significant factors affecting performance are pagination and the presence of visual elements associated with it, especially those spanning multiple pages, such as repeating headers and footers, data regions, tables, lists, or matrices (The Improving Report Rendering Performance topic in SQL Server 2005 Books Online provides more detail on the subject). Another important consideration is the choice of a rendering format, due to its impact on memory consumption (in particular, Excel and PDF introduce more overhead than HTML). In general, you will benefit from an increasing amount of physical memory on the computer hosting the Report Server instance, especially if you make it larger than an estimated size of your biggest reports (which you can determine by examining the ByteCount value of their ExecutionLog entries). Keep in mind that this value might exceed the size of their respective datasets by twofold.

It is fairly common to encounter behavior where the initial generation of a report takes considerably longer than subsequent ones (especially if they are requested within a short period of time). There are two separate mechanisms geared to optimize performance of Reporting Services, which contribute to this disparity. The first one relies on database caching, which stores an intermediate format of newly generated reports in the local database. This, in turn, allows reusing previously processed reports, which otherwise would need to be created anew. A period of caching is determined by the value assigned to the SessionTimeout parameter, in the ConfigurationInfo table in the Report Server database (set to 5 minutes by default). The second one maintains the application (with its worker processes) in memory following its initial load, eliminating the delay associated with re-compiling and re-loading ASP.NET code (which takes place when the Report Server Web site is accessed after a long period of inactivity). In this case, there is an equivalent Idle Timeout parameter, specifying the maximum amount of time that worker processes will remain idle before automatically shutting down. It is possible to either extend it to a desired value or set it to 0, preventing the shutdown altogether - however such decision should take into consideration the potential impact on overall resource utilization and stability.

Starting with Windows Server 2003 (IIS v6), you have the ability to modify Idle Timeout for an application pool that hosts the Reporting Services application. The interface differs depending on the version of operating system and Internet Information Services (IIS) Manager console. In Windows Server 2003, Idle Timeout (minutes) is accessible via the Performance tab of the pool's Properties dialog box, while in Vista, the equivalent option appears in the Process Model section of the pool's Advanced Settings window. Unless you expect a very high number of requests (which is rather unlikely considering that SQL Server 2005 Express Edition is not intended for such scenarios), also make sure to assign the value of 1 to the Maximum number of worker processes in the Web garden section on the Performance tab of the application pool's Properties dialog box. This corresponds to the Maximum Worker Processes in the Process Model section of the application pool's Advanced Settings window in Vista's IIS management console.

When running IIS 6 or later, you should also adjust the recycling of application pools hosting Report Server and Report Manager Web Services (accessible - depending on the operating system and the console version - either via the Recycling tab of their Properties dialog box or the Recycling... link in the Actions pane of Windows Server 2003 and Vista's Internet Information Services (IIS) Manager console). This allows you to assign a schedule, environmental conditions (such as the number of requests or memory usage) or runtime events (configuration changes or unhealthy ISAPI) that trigger the restart of worker processes. A similar setting (controlling the recycle interval of Reporting Server Web Service) labeled RecycleTime is stored in the rsreportserver.config XML file residing in the Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\ReportServer folder. Regardless of the choice, it is important to realize the potential negative impact of modifying their original values.

A more unorthodox approach to mitigating Idle Timeout-related behavior (intended for hosted solutions, where you might not be able to control the value of the Idle Timeout parameter, but admittedly less likely to be relevant in the context of our discussion) involves submitting Web client requests to the Report Server site in regular intervals via an automated, scheduled script, preventing offloading of the corresponding ASP.NET application. Incidentally, this method also might serve as an alternative to report scheduling functionality, missing from SQL Server 2005 Express Edition-based implementation of Reporting Services.

Another factor to consider, when reviewing ASP.NET-related Reporting Services settings, is the value of the executionTimeout parameter (dictating the maximum amount of SOAP message processing time) of the httpRuntime element included in the web.config XML file residing in Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\ReportServer folder. There are also several equivalent parameters that affect behavior of Reporting Services user sessions, such as the DatabaseQueryTimeout element in the rsreportserver.config file in the Program Files\Microsoft SQL Server\MSSQL.x\Reporting Services\ReportServer folder (controlling the Report Server database connection timeouts), as well as SessionTimeout (set by default to 10 minutes, determining the maximum amount of time a session is kept alive between requests) and SessionAccessTimeout (set by default to 5 minutes, enforcing the maximum wait time when attempting to access session information), both stored in the ConfigurationInfo table in the Report Server database.

This concludes our coverage of the Reporting Services component available in SQL Server 2005 Express Edition. In the next article of our series, we will cover its implementation of Full Text Search functionality.

» See All Articles by Columnist Marcin Policht