Troubleshooting Reports: Report Performance
When you view a report, you might experience a long wait time before you see the first page. To help determine where the report processing time is being spent, see Troubleshooting Techniques for Report Problems. After you determine whether the delay time is in data retrieval, report processing, or report rendering, use this topic to help troubleshoot issues.
My data takes too long to retrieve
My report takes too long to process
My report takes too long to render
Design Tips for Optimizing Report Processing
My data takes too long to retrieve.
More report data requires more resource use, more network traffic, more processing time, and more storage. Analyze the issues presented in your report to determine how much data that you need, and then only retrieve that data from the report data sources.
More Data is Retrieved for a Report Than is Needed
Filter, sort, and aggregation is more efficient on the data source than during report processing. Write queries to return only the level of detail that you show in the report. 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. Query parameters are automatically bound to corresponding report parameters, and enable a user to decide which data they are interested in. 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 order-dependent. 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 is much simpler in the report than in the query and is frequently more efficient also.
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, 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 the amount of data needed for a chart or gauge. Drawing hundreds of points in a few pixels on a monitor degrades performance and does not enhance the visual display of the graphics. More than 7 or 8 slices in a pie chart is of questionable value. For more information, see Preparing Data for Display in a Chart Data Region.
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. Although on-demand processing in SQL Server 2008 Reporting Services optimizes data evaluation by processing only data that is visible, all possible data is part of the report. If the user is only interested in seeing detail data some of the time, a drillthrough report is a better choice. For more information, see Types of Reports.
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 Creating, Modifying, and Deleting Snapshots in Report History.
Query Times Out
Query time-out values are specified during report authoring when you define a dataset. The time-out value is stored with the report, in the Timeout element for the Query. By default, this value is set to 30 seconds. For more information, see Setting Time-out Values for Report Processing.
To set the time-out value for a dataset query, see How to: Create a Dataset (Reporting Services).
Large Amounts of Network Traffic Cause Wait Times for the User
Large amounts of data passed as network traffic can introduce wait times for the user. Depending on your expected user base and the expected volume of report views, you can select the appropriate approach for deploying report server components. For more information, see Planning a Deployment Topology.
For example, the following strategies might help reduce the wait times for the user:
Keep the report server catalog on the same computer as the report server.
The report server database tempdb manages report data that is retrieved for each dataset query in a report definition. Keeping report data with the report processor reduces network traffic that can slow report exeuction.
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 ServicesServices that contend for memory on the same server can slow performance.
My report takes too long to process.
Report processing occurs after data is retrieved for report datasets, when the report processor combines the report layout and the data to create an interim report format that is then passed to the report renderer. In general, the report processor combines data and layout only for the current page that is viewed by the user. Report processing time can be affected by report layout, paging, and complex expressions in areas of a report that have many instances.
Use this section to help improve report processing performance.
Expressions in the Page Header or Footer Force All Pages To Be Processed
When you include a reference to the built-in field [&TotalPages], the report processor must paginate the entire report before it can render the first page. If no reference to [&TotalPages] exists, the first page can be rendered and returned to the user immediately, without processing the rest of the report. In addition, the report processor assumes that any complex expression in the page header or footer might contain a direct or indirect reference to [&TotalPages].
To avoid having the report processor paginate a lengthy report, do not include a reference to [&TotalPages] or any complex expressions in the page header and page footer.
No Page Breaks in the Report
As a user pages through a report, the report processor combines data and report layout information for each report page and passes the page to the report renderer. For a report that has no page breaks, the whole report must be processed before the user can view the first page.
A soft-page break renderer, such as the HTML viewer, automatically handles paging for you. You can override this automatic behavior and set the report to be one page by setting the Report property InteractiveHeight to 0. For hard-page break renderers, you must add page breaks manually. For more information about types of renderers, see Understanding Rendering Behaviors.
Verify that InteractiveHeight is not 0 and that it is set to some reasonable page size, for example, 8.5 in. Add page breaks to report items or Tablix groups to help organize the report into pages. This reduces the amount of data that must be processed for each page. For more information, see How to: Add a Page Break (Reporting Services).
Complex Tablix Data Region Grouping and Aggregate Functions
Many levels of nested and adjacent groups in a Tablix 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. For example, Previous is an 'expensive' aggregate function because its value depends on the sorted elements of a data region; Sum is not order-dependent and requires fewer resources. Other post-sort aggregates include First and Last. For more information, see Using Built-in Report and Aggregate Functions in Expressions (Reporting Services).
Evaluate the report design of your report 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.
Many Instances of Subreports in a Tablix Data Region Slow Report Performance
Understand the advantages and disadvantages of using subreports. Each subreport instance is a separate query execution and a separate report processing task.
Do use subreports when there are just a few subreport instances.
Do not use subreports inside a 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 with sales and returns 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 mitigation 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.
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 Report Server Applications.
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 Processing.
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\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\web.config. To set the maximum number of seconds a request can execute, add the httpRuntime element to this file:
<configuration>
. . .
<system.web.
. . .
<httpRuntime executionTimeout="90"/>
. . .
</system.web.
. . .
</configuration>
Depending on the size of the report, this value might have to represent several hours.
My report takes too long to render.
Report rendering occurs after data and layout are combined into an interim format and then passed to a rendering extension. Rendering time can be affected by the amount of data, the number of instances of report items, and paging. When you export a report, you are passing the interim format to a specific renderer. If you know that the users view a report in a specific format, you must optimize the report for that renderer. For more information, see Exporting Reports and Understanding Rendering Behaviors.
Use this section to help improve rendering performance for a report.
Report is Not Optimized for the Chosen Rendering Format
Some features are not supported in all renderers. If the primary format for viewing a report is a specific file format, you might have to modify the report design to optimize the viewing experience for the user.
Add page breaks where it makes sense. For example, each page break defines a new sheet in Excel. Each sheet can handle a maximum of 65000 rows. Consider these limits when you set the page breaks in a report.
For exporting to Excel, do not merge cells in a Tablix data region. In free form reports, align report items vertically. Merged cells and unaligned report items interfere with Excel functionality in the exported report.
HTML parsers are not efficient at rendering very large HTML pages. If you have trouble rendering a report, select a format that produces a smaller file (for example, CSV). 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.
Design Tips for Optimizing Report Processing
If report performance is your top concern, use the following information to help optimize the time that is required to process your report:
For reports that have many instances of text boxes, set CanGrow and CanShrink on text boxes to FALSE. By default, each cell in a Tablix data region contains a text box, so that total number of text boxes that must be rendered can grow quickly.
For reports that have many images, set AutoSize on images to a different value such as Fit.
For text boxes, avoid setting the property TextAlign to General. This value requires conditional processing depending on the text box content.
Avoid horizontal page breaks when they are not required. Review the margins, column widths, and white space in a report. For example, render the report to a .TIFF file and view it in the Microsoft Windows Picture and Fax viewer to determine whether extra pages are being rendered.
Set the KeepTogether property on Tablix members only when you must control the specific rendering behavior for a Tablix data region. The KeepTogether feature requires extra processing when page breaks are calculated.