Troubleshooting Report Performance

  • Article

New: 17 November 2008

This topic describes ways that you can help improve report performance.

To troubleshoot report performance, use Reporting Services log files to determine where most of the time is being spent: in retrieving data, in processing the report layout, or in rendering the report. For more information, see Troubleshooting Techniques for Report Problems.

After you determine where the most time is being spent, use the following sections to help troubleshoot specific issues.

Improving Data Retrieval Performance

Improving Report Processing Performance

Improving Report Rendering Performance

Improving Data Retrieval Performance

More report data uses more resources and more storage, creates more network traffic, and requires more processing time. To help control report performance, design reports that have reasonable amounts of data and complexity. For example, few users want to view a 1,000-page report. Users have difficulty keeping context for a drilldown report if the a table has too many levels of nesting or difficulty scanning a table with too many columns. Pie charts that have hundreds of slices look cluttered and are difficult to read. Analyze your report requirements carefully to determine how much data you need, and then retrieve only that data from the report data sources.

Use the information in the following sections to help reduce the time that is spent retrieving data for your report.

Retrieving More Data Than You Need

It is more efficient to filter, sort, and aggregate large amounts of data on the data source than during report processing. Write queries to return only the data that you want to show in a report. If you plan to display only summary data, calculate aggregates on the data source and do not retrieve detail data. The following list suggests ideas for evaluating each report query in the report:

  • Write queries with WHERE clauses or HAVING clauses that limit the data to just what the user must see in the report. Use query parameters to restrict data that is retrieved at run time. For more information, see Filtering Rows by Using WHERE and HAVING.
    When you create a snapshot report that has report parameters that filter the data, all possible data that could be displayed in the report must be saved in the snapshot. In this case, do not use query parameters in the dataset queries. Instead, manually create report parameters that you can use in filter expressions to enable the user to specify the report data they want.
  • Write queries with the ORDER BY clause to presort data that is retrieved for a report. Sort the data in the order you want it sorted in the report. Presorted data improves report processing time because of the way it is stored in memory. Many report processing tasks do not require sorting data before processing it. For example, SUM is not dependent on sort order. Data within group instances is not automatically sorted. If you do not need sorted data in the report, do not set sort expressions on the dataset or data region. For more information, see ORDER BY Clause (Transact-SQL) and Sorting Data in a Report.
    Sorting groups or sorting by aggregate values, however, is simpler in the report than in the query. Frequently, sorting groups in the report is more efficient than sorting groups in the query.
  • Write queries with GROUP BY to aggregate values on the data source.
    Many times, the most effective way to communicate information is by aggregating values and displaying summaries. You can calculate some level of aggregates on the data source and retrieve them for a dataset. The "detail" data in the dataset now represents aggregates calculated on the data source. For more information about aggregating in the query, see Summarizing Query Results (Visual Database Tools).
    After these pre-aggregated values are in a report, you can continue to aggregate the values as long as you are using an aggregate function that is mathematically transitive, for example, SUM. For example, assume that you have a set of 6 values: 1, 2, 3, 4, 5, 6. If you group the values into pairs, you have a set of 3 values: 3, 7, 11. You can calculate the sum on the first set (21) and calculate the sum of the second set (21) and the sums are the same regardless of the grouping. If you average the values in the sets by using the AVG function, you get a different result for each set. The average for the set of 6 is 21/6 or 3.5. The average of the set of 3 is 21/3 or 7. AVG is not a transitive function.
  • Consider analyzing and optimizing the query performance on the data source. For example, to learn about the query optimizer for SQL Server 2005, see Query Performance and Single SQL Statement Processing.
  • Consider the amount of data needed for a chart. In a line chart, drawing hundreds of points in a few pixels on a monitor degrades performance and does not enhance the visual display of the graphics. In a pie chart, more than 7 or 8 slices is of questionable value.
  • For report items with conditional visibility, the report processor must apply grouping, sorting, and filtering expressions even if only the top level of data is at first visible. If the user is only interested in seeing detail data some of the time, a drillthrough report is a better choice. Drillthrough reports do not run until a user clicks the drillthrough link in the main report. Drilldown reports or subreports process all data even when the data is first hidden. For more information, see Adding Links to a Report.
  • Consider creating execution snapshots for a report. A report snapshot includes all report data retrieved for the datasets in the report definition. For more information, see Report Snapshots.

Large Amounts of Network Traffic Cause Longer Wait Times

Large amounts of data passed as network traffic can introduce wait times for the user. If you know the expected user base and the expected volume of report views, you can select the appropriate approach for deploying report server components.

Consider the following strategies to help reduce wait times for the user:

  • Keep the report server database on the same computer as the report server.
    The report server database tempdb manages report data that is retrieved for each dataset query. Keep tempdb on the report server to reduce network traffic that can slow report execution.
  • For data warehouse data sources, keep the data warehouse on a separate server than the report server.
    Although retrieving data across the network does add an extra task for report execution, having both the data warehouse and Reporting Services on the same server can slow performance because they both contend for memory.

For more information, see Planning a Reporting Services Deployment.

Query Timing Out

If a dataset query times out before it can retrieve data, you can specify a time-out value in the report. By default, this value is set to 30 seconds. To set the time-out value for a dataset query, see How to: Create a Dataset (Report Designer). For more information, see Setting Time-out Values for Report Execution.

Improving Report Processing Performance

Report processing occurs after the data is retrieved for report datasets and report parameters. The report processor combines the report layout and the data to create an interim report format that is then passed to the report renderer. Report processing time can be affected by report layout, paging, and complex expressions in report items that have many instances. Use this section to help improve report processing performance.

Choosing the Right Data Region

Use table and list data regions when possible. Processing a table or list is more efficient than processing a matrix. Table and list data regions support dynamic elements for rows only; matrix layouts support dynamic elements for both rows and columns, which creates a more complex layout structure.

Avoid Total Pages Value in the Page Header or Footer for Physical Page Renderers

A reference to the global field TotalPages can affect report processing performance when the report is rendered by a layout rendering extension that paginates for physical pages, for example, PDF or Image. For more information about renderers, see Design Considerations for Report Rendering.

Using Complex Data Region Grouping and Aggregate Functions

Many levels of nested groups in a table or matrix data region can affect report processing performance. Consider both the level of grouping, the number of group instances, and the use of aggregate functions which require evaluating after group, filter, and sort expressions are applied.

Avoid post-sort aggregates. Post-sort aggregates depend on sort order and include the following functions: Previous, First, Last, and RunningValue. When you include one or more of these functions in an expression, the report processor must sort the target data before applying the function. Where possible, avoid including post-sort aggregates in expressions in matrix layouts that have complex group definitions, such as multiple nested or adjacent groups.

Evaluate the report design and consider whether some data aggregation can occur on the data source. Reducing the amount of data in the report might be sufficient to provide acceptable performance without changing any aggregate function calls.

For more information about aggregate functions, see Using Report Functions in Expressions (Reporting Services).

Specifying Unnecessary Recursion in Expressions

Specify a parent expression for a group only if you are defining a recursive hierarchy, for example, an organizational report that displays managers and employees. The Parent property applies only to recursive data. The Parent property specifically does not apply to the parent-child relationship of nested groups.

Using Subreports in a Data Region with Many Rows

Understand the advantages and disadvantages of using subreports. Each subreport instance is a separate query execution and a separate report processing task.

  • Use subreports in a data region when there are just a few subreport instances.
  • Avoid subreports in a data region group when there are many group instances. For example, to display a list of both sales and returns for each customer, consider using drillthrough reports. Consider whether you can write the query to join the customer data with sales and returns data and then group by the customer ID.
  • Do use subreports when the subreport uses a different data source than the main report. If performance is an issue, consider changing the dataset query in the main report by using one of the following strategies:
    • Collect data in a data warehouse and use the data warehouse as a data source for a single dataset.
    • Use SQL Server linked servers and write a query that retrieves data from multiple databases.
    • Use the OPEN ROWSET capability to specify different databases.

Using Interactive Sort

Avoid interactive sort buttons unless users require the ability to change the sort order of data in the report.

Using Images

Understand the resource requirements for images.

  • Avoid large images including background images. Large images require memory, processing, and rendering resources, especially when they are rendered to hard-copy renderers such as PDF, print, or document images.
  • Avoid many instances of small images from a database or on a server, for example, key performance indicators (KPIs). Include these images as embedded images in the report.
  • For reports that have many images, set AutoSize on images to a different value such as Fit.

Processes Competing for the Same Memory on the Report Server

Multiple applications that compete for the same memory resources on a report server can affect report processing.

Work with the system administrator to verify that the memory management configuration is the correct model for your report server use. For more information, see Configuring Available Memory for Reporting Services.

Report Execution Times Out

In order to run large reports, there are two time-outs you must adjust: report execution time-out and the ASP.NET time-out.

Report execution time-out values are specified on the report server. For more information, see Setting Time-out Values for Report Execution.

ASP.NET time-out policy is controlled by the report server configuration file. The default location for this file is <drive>:\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportServer\web.config. To set the maximum number of seconds a request can execute, set the httpRuntime element to the time-out value in seconds. The following XML fragment shows where to add this element in the configuration file:

<configuration>
   . . .
   <system.web>
      . . .
      <httpRuntime executionTimeout="90"/>
      . . .
   </system.web>
   . . .
</configuration>

For long-running queries or complex reports, you might need to specify a value that represents several hours.

Improving Report Rendering Performance

Report rendering occurs after data and layout are combined and passed to a rendering extension. Rendering time depends on the amount of data, the number of instances of report items, and page sizes. A report renderer determines how much data fits on a page. The definition of a page differs among renderers. A page for the Excel renderer is a worksheet. A page for the PDF renderer, which can print a report, is the physical page. A page for the HTML viewer can be the whole report. Report design for a printed page can differ from report design for online viewing. If you expect that your users will view a report in a specific format, design the report for that format. For more information, see Design Considerations for Report Rendering.

The following table suggests ways to help improve report rendering performance.

Rendering Format Description

All
  • Where possible, avoid repeating header and footer rows for a data region that spans multiple pages. This includes both header and footer rows and group header and footer rows for a table, matrix, or list data region.
  • For reports that have many text boxes, set the text box properties CanGrow and CanShrink to False. By default, each cell in a table or matrix data region contains a text box with CanGrow set to True. When a table or matrix has many rows or columns, the number of text boxes increases quickly.
  • Where possible, avoid setting the KeepTogether property on table, matrix, and list data regions. When KeepTogether is True, the report processor must determine whether the whole data region can fit on a single page, and then determine which page to use.
  • When you design freeform layouts by using list data regions, use rectangles as containers for grouping text boxes. Containers help renderers determine what fits on a page.
  • Charts are generally rendered as images. Avoid nesting a chart in a table or matrix with thousands of rows. When rendered by the HTML viewer, these reports must consume resources when the report processor is retrieving each chart image from the report server. When rendered by other formats, these report must generate each chart image for the output format, which leads to larger output files and longer rendering times.
    Design charts for the target renderer. You do not need the same resolution on a monitor as you do on a printed page. For example, by default, the dots per inch (dpi) setting for a PDF file is 300. You can change this by using the device information settings for the PDF renderer. However, the greater the resolution, the more memory is needed.
    Dd353300.note(en-US,SQL.90).gifNote:

Excel
  • Add page breaks where it makes sense. Each page break defines a new worksheet. Each worksheet can handle a maximum of 65536 rows. For more information, see How to: Add a Page Break (Report Designer).
  • Align report items vertically. The Excel renderer adds extra cells where they are required to align the edges of report items on the report design surface. This causes merged cells in the Excel worksheet. To avoid merged cells, vertically align report item edges with table and matrix columns. To avoid rounding errors that occur when you specify units in inches (in) and fractions of inches, specify the units in points (pts). The default units for Excel are points.
  • For text boxes, avoid setting the property TextAlign to General. This value requires conditional processing depending on the text box content.

HTML
  • For large reports, avoid setting the report property InteractiveHeight to 0. HTML renderers are not efficient for very large HTML pages. When InteractiveHeight is 0, the whole report is treated as one page.

PDF

Image

TIFF

Print
  • Avoid horizontal page breaks. To remove extra horizontal pages, review the margins, column widths, and white space in a report. The report processor preserves the white space that you see on the report design surface. To verify your changes, export the report to a TIFF file and view it in the Microsoft Windows Picture and Fax viewer.
  • Avoid including a reference to the built-in field TotalPages in the page header or page footer. Renderers that display reports on physical pages must process a report with this value twice: once to determine the total number of pages, and a second time to render each page with the correct value.
  • Where possible, avoid setting the report item property RepeatWith to True for reports that span multiple horizontal pages.
  • Verify that the page size is set to a reasonable value, for example, 8.5 in.

If you have trouble rendering a report in one format, select a format that produces a smaller file, for example, CSV. For a published report, you can specify a rendering format in the URL. For more information, see Specifying a Rendering Format in a URL.

If you cannot select another format because the report toolbar is not available, you can define a subscription to set a rendering format and deliver the report as a static document to a file share. For more information, see File Share Delivery in Reporting Services.

See Also

Concepts

Reporting Services Log Files
Processing Large Reports

Other Resources

Troubleshooting Reporting Services
Reporting Services Errors and Events
Troubleshooting Report Problems

Help and Information

Getting SQL Server 2005 Assistance