Author: Carl Rabeler

Reviewers: Prash Shirolkar, Robert Bruckner, Dean Kalanquin, Guilherme Boreki, Tanka Sunuwar, Brian Hartman, Craig Guyer, Kevin Cox, Chuck Heinzelman, Paul Mestemaker

Overview

Integrating Microsoft SQL Server Reporting Services (SSRS) with your Microsoft SharePoint environment provides many benefits to your enterprise, including specifically shared storage, shared security, and same site access for all business documents. For more information about the benefits of Reporting Services integration with SharePoint, see Edgenet Realizes the Power of Dynamic IT and Self-Service BI and Overview of Reporting Services and SharePoint Technology Integration. SharePoint integration does, however, introduce some performance overhead. Naturally, the performance of Reporting Services reports in SharePoint integrated mode is an area of concern for customers. Since the initial release of software that integrated Reporting Services with SharePoint approximately five years ago, Microsoft has improved report performance (for both native and SharePoint integrated mode) through service packs and with new releases of SQL Server, SharePoint, and Windows. After the release of SQL Server 2008 R2 and SharePoint 2010, the SQL CAT and Reporting Services teams collaborated to measure the performance of Reporting Services reports in SharePoint integrated mode and in native mode to more fully understand and document the current performance landscape. We performed concurrent throughput and individual report performance tests against Reporting Services in SQL Server 2008 compared with Reporting Services in SQL Server 2008 R2, specifically focused on the performance delta between native mode and SharePoint mode.

Executive Summary

Reporting Services in SQL Server 2008 R2 and SharePoint 2010 (with Windows Server 2008 R2) delivers significant performance improvements compared with SQL Server 2008 and SharePoint 2007 (with Windows Server 2008) in both native and SharePoint integrated mode. These performance improvements include improvements in overall throughput (operations per second) and in individual report performance. There is a measurable amount of additional overhead associated with SharePoint integrated mode that impacts report performance (between 250 milliseconds and 500 milliseconds of additional overhead to view the first page of any report). This performance difference is most evident when users request sub-second reports; in this case, the SharePoint overhead is a substantial percentage of the overall execution time. When users request multiple-second reports, the performance difference is a small fraction of the total time required to render the first page, and, when they exporting to Microsoft Excel, SharePoint integrated mode in SQL Server 2008 R2 Reporting Services is equal to or faster than native mode with SQL Server 2008 Reporting Services.

Furthermore, the performance of individual reports viewed using the ASP.NET Ajax-enabled Report Viewer web part in SharePoint 2010 degrades significantly when there are multiple viewers on a single web page, the viewer generates very large HTML, and the web page is viewed using older browsers. This is an area that we are working very hard to resolve, both through Cumulative Updates and in future releases (it is a multi-faceted issue). For more information about fixes in Cumulative Update 3 related to this issue, see It takes a long time to open a SQL Server 2008 R2 Reporting Services report that contains many parameter values in Internet Explorer.

Important: The additional overhead associated with SharePoint integrated mode will vary depending on server hardware, network infrastructure, and other factors, such as report design, all-in-one vs distributed architecture, SharePoint response time, anti-virus software, etc. Due to this variety of factors, the amount of additional overhead that will be experienced in any specific environment cannot predicted or guaranteed.

Test Topology

We utilized the following topologies to test Reporting Services in SQL Server 2008 (with Service Pack 1).

Note: For Reporting Services in SQL Server 2008, we utilized Windows Server 2008. For Reporting Services in SQL Server 2008 R2, we utilized Windows Server 2008 R2.

Native Mode

The following diagram illustrates our environment for testing native mode performance.

In this topology, each report request from our simulated user requires a minimum of four network hops to fulfill the report request, with two additional network hops required for the reports that were exported:

1. Report request from user to Reporting Services engine
2. Reporting Services engine retrieval of report definition from RS catalog server
3. Reporting Services engine query request to data source
4. Return of the rendered report from the Reporting Services engine to the user
5. [If applicable] Request from user to Reporting Services engine to render and export in a specified format
6. [If applicable] Return of the report in the desired export format from the Reporting Services engine to the user

Important: All report requests are handled directly by the Reporting Services engine.

SharePoint Mode

The following diagram illustrates our environment for testing SharePoint integrated mode.

(SP stands for SharePoint.) In this topology, each report request from our simulated user requires a minimum of seven network hops to fulfill the report request, with four additional network hops required for the reports that were exported:

1. Report request from user to the SharePoint web service
2. SharePoint engine retrieval of the report definition from the SharePoint catalog (the master copy)
3. SharePoint web service report request to Reporting Services engine
4. Reporting Services engine query to the RS catalog server to verify that the report definition exists in the RS catalog and that it is same as the master copy
5. Reporting Services engine query request to data source
6. Reporting Services forward of rendered report to the SharePoint web service
7. Return of the report from the SharePoint web service to the user
8. [If applicable] Request from user to the SharePoint web service to render and export in a specified format
9. [If applicable] Return of the report in the desired export format from the Reporting Services engine to the SharePoint web service
10. [If applicable] Return of the report in the desired export format from the SharePoint web service to the user

Important: The report requests are handled initially by the SharePoint web service and then by the Reporting Services engine. There is basic overhead of communication with the SharePoint object model for doing basic work such as getting the item and checking access. There is also overhead associated with additional network hops, with the amount of this overhead dependent upon your SharePoint topology and performance capabilities of your network.

Test Workload

We utilized 53 different reports that exercised a wide range of product features and typical report design practices, including:

Tables
Matrixes
Charts
Rich text
Recursive aggregates
Running value aggregates
Images
Recursive hierarchies
Filtered data sets
Complex expressions
Recursive drilldowns
Subreports
Show / hide
Gauges
Calculated fields
Conditional formatting

These reports consisted of mix of large reports (roughly 250,000 rows) and small reports (roughly hundreds to thousands of rows).

Important: This workload, with its mix of product features of report sizes, was specifically designed to place a heavy load on the system; it does not necessarily simulate a typical real-world workload. This workload included only product features supported by SQL Server 2008 Reporting Services and SQL Server 2008 R2 Reporting Services.

Concurrent Throughput Performance

The goal of this test was to measure Reporting Services performance from the point of view of the IT department and total workload. The following sections describe the concurrent throughput performance workload and test results.

Concurrent Throughput Performance Workload

We designed a concurrent throughput performance workload based on the test workload. Approximately one-third of the workload was rendered from snapshots and two-thirds of the workload was rendered from live data. Using a custom test harness to randomize the report executions, the workload had the following characteristics:

Approximately 50 percent of the reports were rendered to HTML from live data.
Approximately 20 percent of the reports were rendered and then exported to PDF from live data.
Approximately 20 percent of the reports were rendered and then exported to PDF from snapshots.
The remaining 10 percent of the reports were rendered and then exported to CSV, Excel, XML, and Microsoft Word formats equally from live data and from snapshots.

Important: This concurrent throughput performance workload does not necessarily simulate a typical real-world workload, with its mix of report types, report sizes, mix of live versus snapshot data, and mix of export formats.

Note: The live reports used the rs:ClearSession URL parameter to render reports from live data rather than cache.

We tested different concurrency levels (n). The number of users (the factor of n) in your environment depends, of course, on your server hardware, network speed, data sources, and the actual reports.

Concurrent Throughput Performance Test Results

Reporting Services in SQL Server 2008 R2 delivers significant concurrent throughput performance improvements, in both native mode (with Windows Server 2008 R2) and SharePoint mode (with Windows Server 2008 R2 and SharePoint 2010). Using our specific workload, we observed the following:

SharePoint Mode: Throughput improvements occur with Reporting Services in SQL Server 2008R2 in SharePoint integrated mode at all concurrency levels, beginning at a 12 percent increase in concurrent operations at 1x concurrent users and increasing to a 33 percent increase in throughput at 10x concurrent users when compared with Reporting Services in SQL Server 2008.
Native Mode: Throughput improvements appear gradually with Reporting Services in SQL Server 2008 R2 in native mode as concurrency increases, with a 22 percent increase in concurrent operations at 10x concurrent users when compared with Reporting Services in SQL Server 2008.

The test results are displayed in the following graph.

(SSRS 2008 stands for SQL Server 2008 Reporting Services, and SSRS 2008 R2 stands for SQL Server 2008 R2 Reporting Services.) We found the following observations most interesting:

Whereas SQL Server 2008 Reporting Services in SharePoint integrated mode performed fewer operations per minute than SQL Server 2008 Reporting Services in native mode even at low concurrency, SQL Server 2008 R2 Reporting Services in SharePoint mode performed approximately the same number of operations per minute as SQL Server 2008 R2 Reporting Services in native mode until the system reached higher levels of concurrency.
SQL Server 2008 R2 Reporting Services in SharePoint mode performed more operations per minute than SQL Server 2008 Reporting Services in native mode until the system reached higher levels of concurrency.
At the highest levels of concurrency that we utilized in this test, all of the workloads began to level out in performance. Our investigation showed us that our bottlenecks at high concurrency were in the data source and the network (our goal in this test was not to tune the data source or the network).

Important: The actual performance improvements that you experience with your actual workloads will vary depending upon your workload and your environment. These performance numbers are not based on real workloads, but rather on a workload that was designed to test the entire range of the product's features and capabilities.

Individual Report Performance

The goal of this test was to measure Reporting Services performance from the point of view of the user executing the report. The following sections describe the individual report performance workload and test results.

Individual Report Performance Workload

We executed each of the reports in the test workload serially using Microsoft Internet Explorer 8, executing against Reporting Services in both native mode and SharePoint integrated mode. We measured:

Time to load the initial viewer frame
Time to load the first page
Time to load the remaining pages

We also executed each of the reports in the test workload using Internet Explorer running against a single dashboard page in SharePoint containing multiple Ajax Report viewers.

Individual Report Performance Test Results

Reporting Services in SQL Server 2008 R2 delivers significant individual report performance improvements, in both native mode (with Windows Server 2008 R2) and SharePoint mode (with Windows Server 2008 R2 and SharePoint 2010).

Using our specific workload, we observed that individual report performance improvements occur with Reporting Services in SQL Server 2008 R2 in both SharePoint mode and native mode. The performance improvement ranged from no improvement for some reports to improvements above 30 percent for a handful of reports.

The test results are displayed in the following graphs, with the results grouped based on the report execution time for reports in native mode in SQL Server 2008 Reporting Services.

Rendering the First Page of a Report

The following chart shows the average time required to return the first page of the requested report to Internet Explorer. We focused initially on the time to return the first page of a report because if the first page of a report renders too slowly, the user experience is not satisfactory.

Notice that Reporting Services in SQL Server 2008 R2 is faster than in SQL Server 2008 for each mode. Notice also that native mode is faster than SharePoint integrated mode, but that the impact of that difference depends on the length of time to render the first page. From the individual user perspective, users will experience the performance delta between SharePoint integrated mode and native mode primarily with reports that take less than a few seconds to return the first page because in these cases the SharePoint overhead is responsible for the bulk of the execution time to render the first page.

Note: We observed that the overhead for SharePoint to establish the connection from the web front end to Reporting Services and return results was approximately 400 milliseconds for each report, regardless of size of the report rendered.

Exporting a Report to Excel

The following chart shows the average time required to export the requested report to Excel for reports of various execution time ranges. We focused on export to Excel as the most common export format, and this provides a good measure of the time to render, paginate, and format the entire report.

(R1 Native and R2 Native stand for SQL Server 2008 Reporting Services and SQL Server 2008 R2 Reporting Services in native mode, and R2 SP stands for SQL Server 2008 R2 in SharePoint integrated mode.) Notice again that Reporting Services in SQL Server 2008 R2 is faster that in SQL Server 2008 for each mode. Notice also that native mode is faster than SharePoint integrated mode for the export phase for the sub-second reports, but that SharePoint integrated mode for Reporting Services in SQL Server 2008 R2 is actually faster that native mode for Reporting Services in SQL Server 2008 for reports running longer than two seconds.

Multiple AJAX Viewers

Viewing the reports in a multiple viewers in a single dashboard in SharePoint revealed that Internet Explorer (as well as other browsers) had performance issues with large reports generating excessively large HTML in some circumstances. While this does not result in a functional problem, some browsers are not able to handle the size of the HTML well. As a result, we are seeing varying degrees of performance degradation on different browsers. This performance problem seems to be particularly noticeable on pages with a larger number of valid values in a parameter, a large report page, or multiple viewers (or web parts) on a single page. In the meantime, we recommend using the latest version of your browser, because the problem appears to be more significant on older browsers, and ensuring that you are using the newest Cumulative Update. For more information about fixes in Cumulative Update 3 related to this issue, see It takes a long time to open a SQL Server 2008 R2 Reporting Services report that contains many parameter values in Internet Explorer.

Conclusion

Reporting Services in SQL Server 2008 R2 and SharePoint 2010 (with Windows Server 2008 R2) delivers significant performance improvements compared with SQL Server 2008 and SharePoint 2007 (with Windows Server 2008) in both native and SharePoint integrated mode. These performance improvements include improvements in overall throughput (operations per second) and in individual report performance. There is a measurable amount of additional overhead associated with SharePoint integrated mode that impacts report performance (between 250 milliseconds and 500 milliseconds of additional overhead to view the first page of any report). This performance difference is most evident when users request sub-second reports; in this case, the SharePoint overhead is a substantial percentage of the overall execution time. When users request multiple-second reports, the performance difference is a small fraction of the total time required to render the first page, and, when exporting to Excel, SharePoint integrated mode in SQL Server 2008 R2 Reporting Services is equal to or faster than native mode with SQL Server 2008 Reporting Services.

Technical Notes

Reporting Services Performance in SharePoint Integrated Mode in SQL Server 2008 R2