Behind-the-scenes components of Reporting Services affect overall system performance. A basic knowledge of how to use these components, how to set their properties, and what to turn off are important in optimizing system performance. I discuss how to deal with these components in the following sections.
Configuring and running large reports
If you're working with a large report, you must choose report generation, rendering, and delivery options that can accommodate large documents. Report size is determined by the row set that comes back from the query. To estimate the size of a report after it is processed, review the row count returned from the query. If it is many thousands or hundreds of thousands of rows, I provide you with some recommendations in the list that follows. For reports that contain volatile data, report size can change dramatically from one report run to the next. Be sure to monitor the data source to determine how data volatility affects your report and whether you need to follow the steps I prescribe for large reports.
Here are some general recommendations when you're considering how to configure and run large reports:
Design the report to support pagination. The report server sends a report one page at a time. If the report includes pagination, you can control how much data is streamed to the browser.
Configure the report to run as a report execution snapshot. Use this option if you can't add page breaks. Do not set a time-out value for report execution. Use a schedule to determine when the report data is refreshed.
Never run a large report on demand because it almost never succeeds. Configuring a report to run as a report execution snapshot prevents it from running on demand. The HTML rendering format used to initially render a report opens the report in a browser, and most browsers cannot accommodate very large documents. For example, a report that contains 5,000 rows of data almost certainly cannot be viewed in a browser in a single page.
Consider distributing a report as a file share on a file directory. If the report is very large, it will hang the browser when a user opens the report in Report Manager.
Configure the report to use a shared data source if you want flexibility in determining whether the report is processed. One advantage to using a shared data source is that you can disable it so that it cannot be used to get data for the report. Disabling the data source prevents report processing.
Use stored credentials for the data source connection for security and to enable data-driven subscriptions for the report.
Disable report history (optional) if you want to conserve disk space. The recommendations for report distribution offered later in this chapter (see the "Distributing reports" section) provide an alternative to storing a large report in report history. To disable report history, clear all the check boxes on the History properties page.
Configure the report to use item-level security. Limit access to users who define the subscription and manage the report.
Specify item-level security to allow you to control access to the report. By default, users can open any report that they can view in the folder hierarchy. Even if you configure a report to run as a snapshot, users who can view the report item in a folder can open the report.
To restrict access to the report, edit item security by replacing the default role assignments with new ones that allow access to just those users who need to create the subscription or manage the report.
Leverage the query and execution timeout settings for long-running reports that process during the business day. You want to ensure that the report does not un duly hamper overall system performance during a normal business day of report processing.
The previous tip was excerpted from Chapter 14, 'Optimizing Report Performance,' from the book "Microsoft SQL Server 2005 Reporting Services for Dummies" by Mark Robinson, courtesy of Wiley Publishing. Click here for the complete collection of book excerpts.
Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning