Author: Carl Rabeler
Test Contributers: Jim Carroll, David Raskino, Bill Emmert, Dan Manrique, Jim Dugan
Technical Reviewers: Robert Bruckner, David Raskino, Lukasz Pawlowski, Burzin Patel, Kun Cheng, Thierry D'Hers, Prem Mehra, Kevin Cox

Introduction

Microsoft SQL Server 2008 Reporting Services contains many improvements designed to improve performance and capacity. The rearchitecture of the Reporting Services 2008 server as a single server with embedded HTTP capabilities enables Reporting Services 2008 to exercise more control over resource management (thread, memory, and state management) to improve performance and scalability, as well as configurability. The new report engine architecture includes a new on-demand processing model designed to ensure that sufficient memory is always available to execute very large reports and heavy workloads from a large number of concurrent users (by paging and releasing memory in response to memory pressure). Using an early RC0 build (which was not an optimized build), we tested the performance of SQL Server 2008 Reporting Services versus SQL Server 2005 Reporting Services, SP2 (which was optimized) on several scale-up hardware platforms. This technical note discusses our tests and our learnings.

Executive Summary

Reporting Services 2008 was able to respond to 3–4 times the total number of users and their requests on the same hardware without HTTP 503 Service Is Unavailable errors compared with Reporting Services 2005, regardless of the type of renderer. In stark contrast, Reporting Services 2005 generated excessive HTTP 503 Service Is Unavailable errors as the number of users and their requests increased, regardless of the report renderer.

Our tests clearly show that the new memory management architecture of the report server enables Reporting Services 2008 to scale very well, particularly on the new four-processor, quad-core processors. With our test workload, Reporting Services 2008 consistently outperformed SQL Server 2005 with the PDF and XLS renderers on the four-processor, quad-core hardware platform (16 cores) both in terms of response time and in terms of total throughput. Furthermore, with these renderers on this hardware platform, Reporting Services dramatically outperformed other hardware platforms regardless of Reporting Services version, responding to 3–5 times the number of requests than when running on either of the other hardware platforms. As a result, we recommend that you scale up to four-processor, quad-core servers for performance and scale out to a two-node deployment for high availability. Thereafter, as demand for more capacity occurs, add more four-processor, quad-core servers.

Finally, with all renderers and with all hardware platforms using our test workload, the performance bottlenecks were the processor on the front-end server and the disk subsystem on the data source with Reporting Services 2008, whereas the Reporting Services front-end Web service was the performance bottleneck with Reporting Services 2005.

Note: This technical note assumes that you are familiar with the following articles:

· Planning for Scalability and Performance with Reporting Services

· Reporting Services Scale-Out Architecture

Test Environment

We used a standard remote catalog environment for all testing. Figure 1 illustrates the test environment for our scale-up performance tests. For our Reporting Services 2008 tests, SQL Server 2008 components were installed on all servers and for our Reporting Services 2005 tests, SQL Server 2005 components were installed on all servers. Windows Server 2003 was used for all our tests.

Figure 1: Reporting Services Scale-Up Architecture with Remote Catalog and Remote Data Source

Reporting Services “Front-End” Servers

For the Reporting Services front-end server, we tested Reporting Services 2008 and Reporting Services 2005 on the following three hardware platforms:

· Hardware Configuration #1 (2x2): One HP Proliant BL 460c G1 computer with two x64 dual core processors and 16 GB RAM

· Hardware Configuration #2 (4x2): One Dell 2970 computer with four x64 dual core processors and 16 GB RAM

· Hardware Configuration #3 (4x4): One HP Proliant BL 460c G5 computer with four x64 quad core processors and 16 GB RAM

Reporting Server Catalog Servers

For the Report Server catalog server, we used one HP Proliant BL 460c G1 computer with two x64 dual core processors and 8 GB RAM for all tests. The Report Server catalog server was connected to an EMC Clarion CX3 Model 40 SAN.

Reporting Data Source Server

For the Report Server data source server, we used one HP Proliant BL 460c G5 computer with four x64 quad core processors and 16 GB RAM for all tests. The Report Server data source server was connected to an EMC Clarion CX3 Model 40 SAN.

Important: We used the same configuration for the Report Server catalog server and the Report Server data source server for all tests. Our testing with Reporting Services 2008 revealed that disk queuing was a performance bottleneck and that even closer attention must be paid to disk configuration for optimum performance with Reporting Services 2008 than with Reporting Services 2005. This close attention includes the disk configuration for the source database(s) as well as the ReportServer and ReportServerTempDB databases. For more information about optimizing storage for best performance, see Storage Top 10 Best Practices and Predeployment I/O Best Practices.

Test Harness

For our performance tests, we used Visual Studio Team System 2008 Test Edition (VSTS) to test a report load based on the Adventure Works sample relational database. For our tests, we created 50 separate reports of varying complexity and size and designed for different user personas, such as Executive, Territory Manager, Product Manager, and Sales Manager. The report load consisted of:

· A mix of execution modes with 95% live and 5% snapshot

· A mix of data region types with 80% table and 20% chart

· A mix of query data sizes with 60% small, 20% medium, and 20% large

All tests were executed using the same mix of these reports. These reports were executed with a think time of 20 seconds between the execution of each report and we ramped up to a specified number of users. While we conducted our tests with different numbers of users, this technical note focuses primarily on our 5,000-user test as the most representative of the differences and similarities between Reporting Server 2008 and Reporting Services 2005 (Ramp Up Test). At the end of this technical note, we discuss the results of a push test to determine how far we can push Reporting Services 2008 compared to SQL Server 2005, and to observe where the bottleneck occurs with each version of Reporting Services (push test). We executed test runs for the following report renderers:

· HTML

· PDF

· XLS

· Mix (approximately 33% of each renderer)

Note: We modified the MaxActiveReqForOneUser configuration setting for this test to enable us to test with this number of users.

Test Results

The following sections discuss the results that we observed with each report renderer on each hardware platform.

Ramp Up Test with a Mix of Report Renderers

With a mix of report renderers, Reporting Services 2008 outperformed Reporting Services 2005 on the 4x4 and 4x2 hardware platforms, with the 4x4 hardware platform substantially outperforming the 4x2 and 2x2 hardware platforms regardless of the Reporting Services version. Figures 2, 3 and 4 graphically display the errors/sec, average response time, and total requests counters for these hardware platforms for each version of Reporting Services.

Errors/Sec

When we analyzed the errors/sec counter values from our ramp up test with a mix of report renderers, we discovered that excessive HTTP 503 Service Is Unavailable errors occurred during the Reporting Services 2005 tests on all hardware platforms at approximately 4,500 users (see Figure 2). During the Reporting Services 2008 tests on the three hardware platforms with the mix of report renderers, we observed that no such hard limit was hit. We observed that this same limit was hit with all report renderers during our Reporting Services 2005 tests and was not hit with any of our Reporting Services 2008 tests. Reporting Services 2008 manages memory under heavy load much better than Reporting Services 2005, and it avoids overloading the Reporting Services front-end server. The excessive HTTP 503 errors with Reporting Services 2005 indicate that the heavy load coming from the clients overloaded the Reporting Services front-end server to the point where the server is simply starved for resources, and reveal that memory management under load is clearly a problem with Reporting Services 2005.

Note: The Errors/sec counter value includes HTTP errors (HTTP 500 and HTTP 503 errors). HTTP 503 errors with Reporting Services tell you that the Web service itself is unable to respond (frequently due to insufficient memory). HTTP 500 errors with Reporting Services tell you that the Web service was unable to complete the request for some unknown reason (generally due hardware bottlenecks on the front-end server, the network or the back-end server). During the push test discussion (later in this technical note), we analyze in more detail the bottlenecks we saw with Reporting Services 2008 that resulted in HTTP 500 errors.

Figure 2: Mix of Renderers—Errors/Sec

Average Response Time

When we analyzed the average response time counter values from our ramp up test with a mix of report renderers, we discovered that the average response time with Reporting Services 2008 was better than with Reporting Services 2005 on the 4x4 and 4x2 hardware platforms (see Figure 3). We discovered no significant difference in the average response time on the 2x2 hardware platform. Furthermore, we discovered that the 4x4 hardware platform substantially outperformed either of the other two hardware platforms with either Reporting Services 2008 or Reporting Services 2005. Finally, we also noticed that the improvement of the average response time of Reporting Services 2008 on the 4x2 hardware platform over Reporting Services 2005 became more significant as the number of users submitting requests increased.

Figure 3: Mix of Renderers—Average Response

Note: The chart lines for the Reporting Services 2005 tests in Figure 3 only include the response times for the user load before the number of errors/sec climbed to the point where they completely skewed the overall response time numbers.

Total Requests

When we analyzed the total requests counter values from our ramp up test with a mix of report renderers, we discovered that not only did Reporting Services 2008 handle a greater number of users submitting reports without generating excessive timeout or service unavailable errors and render those reports with a better average response time, Reporting Services 2008 also sustained a higher throughput than Reporting Services 2005 on the 4x4 and 4x2 hardware platforms (see Figure 4). This increased throughput was most substantial on the 4x4 hardware platform, on which the throughput for the duration of the test was approximately 40% better with Reporting Services 2008 than with Reporting Services 2005. The new memory management capabilities of Reporting Services 2008 enabled the superior processing capability of the 4x4 hardware platform to perform substantially better than Reporting Services 2005 on the same platform.

Figure 4: Mix of Renderers—Total Requests

Note: The chart lines for the Reporting Services 2005 tests in Figure 5 only include the total requests for the user load before the number of errors/sec climbed to the point where they completely skewed the overall total request numbers. On all hardware platforms, Reporting Services 2008 kept on responding to users after Reporting Services 2005 was unable to continue due to HTTP 503 Service Is Unavailable errors.

Ramp Up PDF Report Renderer Test

With the PDF report renderer, Reporting Services 2008 outperformed Reporting Services 2005 on the 4x4 and 4x2 hardware platforms, with the 4x4 hardware platform substantially outperforming the 4x2 and 2x2 hardware platforms regardless of the Reporting Services. Figures 5 and 6 graphically display the average response time and total requests counters for each hardware platform for each version of Reporting Services. With the PDF report renderer, until the HTTP 503 error threshold discussed previously was hit with Reporting Services 2005, the processor was the performance bottleneck.

Average Response Time

When we analyzed the average response time counter values from our ramp up test with the PDF report renderer, we discovered that the average response time with Reporting Services 2008 was better than with Reporting Services 2005 on the 4x4 and 4x2 hardware platforms (see Figure 5). Furthermore, we discovered that the 4x4 hardware platform substantially outperformed either of the other two hardware platforms with either Reporting Services 2008 or Reporting Services 2005.

Figure 5: PDF Renderer—Average Response

Note: The chart lines for the Reporting Services 2005 tests in Figure 5 only include the response times for the user load before the number of errors/sec climbed to the point where they completely skewed the overall response time numbers. On all hardware platforms, Reporting Services 2008 kept on responding to users after Reporting Services 2005 was unable to continue due to HTTP 503 Service Is Unavailable errors.

Total Requests

When we analyzed the total requests counter values from our ramp up test with the PDF report renderer, we discovered that Reporting Services 2008 responded to a significantly greater number of requests compared to Reporting Services 2005 over the same amount of time only on the 4x4 hardware platform (see Figure 6).

Figure 6: PDF Renderer—Total Requests

Note: The chart lines for the Reporting Services 2005 tests in Figure 6 only include the total requests for the user load before the number of errors/sec climbed to the point where they completely skewed the overall total request numbers. On all hardware platforms, Reporting Services 2008 kept on responding to users after Reporting Services 2005 was unable to continue due to HTTP 503 Service Is Unavailable errors.

Ramp Up XLS Report Renderer Test

With the XLS report renderer, Reporting Services 2008 outperformed Reporting Services 2005 primarily on the 4x4 hardware platform, with the 4x4 hardware platform substantially outperforming the 4x2 and 2x2 hardware platforms regardless of the Reporting Services version. Figures 7 and 8 graphically display the average response time and total requests counters for each hardware platform for each version of Reporting Services. With the XLS report renderer, until the HTTP 503 error threshold discussed previously was hit with Reporting Services 2005, the processor was the performance bottleneck.

Average Response Time

When we analyzed the average response time counter values from our ramp up test with the XLS renderer, we discovered that the average response time with Reporting Services 2008 was better than with Reporting Services 2005 on the 4x4 and 2x2 hardware platforms (see Figure 7). Furthermore, we discovered that the 4x4 hardware platform substantially outperformed either of the other two hardware platforms with either Reporting Services 2008 or Reporting Services 2005. Finally, we also noticed that (contrary to our tests with the mix of renderers and with the PDF report renderer) the average response time of Reporting Services 2008 and Reporting Services 2005 on the 2x2 hardware platform was somewhat better than on the 4x2 hardware platform, with Reporting Services 2005 outperforming Reporting Services 2008 on this platform. This performance difference was not expected and we did not have time to investigate precisely why this performance difference was observed.

Figure 7: XLS Renderer—Average Response

Note: The chart lines for the Reporting Services 2005 tests in Figure 7 only include the response times for the user load before the number of errors/sec climbed to the point where they completely skewed the overall response time numbers. On the 4x4 hardware platform, Reporting Services 2008 kept on responding to users after Reporting Services 2005 was unable to continue due to HTTP 503 Service Is Unavailable errors. Notice that the HTTP 503 error threshold with Reporting Services 2005 was only hit on the 4x4 hardware platform; on the other hardware platforms using the XLS report renderer with Reporting Services 2005, the processor was the performance bottleneck.

Total Requests

When we analyzed the total requests counter values from our ramp up test with the XLS renderer, we discovered that Reporting Services 2008 responded to a significantly greater number of requests compared to Reporting Services 2005 over the same amount of time only on the 4x4 hardware platform (see Figure 8). Finally, we noticed that the total requests of Reporting Services 2008 and Reporting Services 2005 on the 2x2 hardware platform were slightly higher than on the 4x2 hardware platform.

Figure 8: XLS Renderer—Total Requests

Note: The chart lines for the Reporting Services 2005 tests in Figure 8 only include the total requests for the user load before the number of errors/sec climbed to the point where they completely skewed the overall total request numbers. On the 4x4 hardware platform, Reporting Services 2008 kept on responding to users after Reporting Services 2005 was unable to continue due to HTTP 503 Service Is Unavailable errors. Notice that the HTTP 503 error threshold with Reporting Services 2005 was hit only on the 4x4 hardware platform; on the other hardware platforms using the XLS report renderer with Reporting Services 2005, the processor was the performance bottleneck.

Ramp Up HTML Renderer Test

With the HTML report renderer, Reporting Services 2008 did not significantly outperform Reporting Services 2005 on any of the hardware platforms. Furthermore, the 4x4 hardware platform did not substantially outperform either the 4x2 or the 2x2 hardware platforms regardless of the Reporting Services version. Figures 9 and 10 graphically display the average response time and total requests counters for each hardware platform for each version of Reporting Services.

Note: It should be noted again here that our analysis of the performance counters on the Report Server catalog server as well as on the reporting data source server indicated that these tiers were a performance bottleneck with Reporting Services 2008 and were not a performance bottleneck with Reporting Services 2005. Based on our workload and our hardware/software configuration for these tests, Reporting Services 2008 could have performed even better than it did with more tuning and/or with more hardware capability on these tiers than Reporting Services 2005. We do not know, with more capacity on the back-end servers, whether the HTML renderer on Reporting Services 2008 would have outperformed the HTML rendered on Reporting Services 2005. Further testing in this area is needed.

Average Response Time

When we analyzed the average response time counter values from our ramp up test with the HTML renderer, we discovered that Reporting Services 2008 on the 4x2 and the 2x2 hardware platforms had a better average response time than Reporting Services 2005 on the same hardware platform. However, Reporting Services 2008 on the 4x4 hardware platform suffered from degradation in response times as the number of users and the workload increased (see Figure 9). We are not sure why this degradation occurred and the root cause requires more analysis and testing.

Figure 9: HTML Renderer—Average Response

Note: The chart lines for the Reporting Services 2005 tests in Figure 9 only include the response times for the user load before the number of errors/sec climbed to the point where they completely skewed the overall response time numbers. On all hardware platforms, Reporting Services 2008 kept on responding to users after Reporting Services 2005 was unable to continue due to HTTP 503 Service Is Unavailable errors.

Total Requests

When we analyzed the total requests counter values from our ramp up test with the HTML renderer, we discovered that Reporting Services 2008 did not render a significantly higher number of reports in the same amount of time compared to Reporting Services 2005 (see Figure 10) until after the HTTP 503 error threshold was reached.

Figure 10: HTML Renderer—Total Requests

Note: The chart lines for the Reporting Services 2005 tests in Figure 10 only include the total requests for the user load before the number of errors/sec climbed to the point where they completely skewed the overall total request numbers. On all hardware platforms, Reporting Services 2008 kept on responding to users after Reporting Services 2005 was unable to continue due to HTTP 503 Service Is Unavailable errors.

Push Test

We also performed some limited testing to test how far we could push the 4x4 hardware platform without significant errors. To accomplish this, we modified our VSTS test to eliminate the 20-second think time. We tested with the mix of report renders and then pushed the 4x4 hardware platform for 25 minutes, ramping up to 10,000 users over the first 10 minutes and then continuing at that number of users for the duration of the push test.

Errors/Sec

When we analyzed the errors/sec counter values from our push test with a mix of report renderers, we discovered that excessive HTTP 503 Service Is Unavailable errors occurred during the Reporting Services 2005 tests on all hardware platforms at 9 minutes into our test at approximately 9,000 users (see Figure 11). During the Reporting Services 2008 push test on the 4x4 hardware platform, we observed that no such hard limit was hit.

Figure 11: Push Test with Mix of Report Renderers—Errors/Sec

Http Errors vs. Request Time-Out Errors

To better understand the hardware limits we hit with Reporting Services 2008 compared to Reporting Services 2005, we compared the HTTP 503 errors we saw with Reporting Services 2005 with the Reporting Services time-out errors that we saw with Reporting Services 2008. We discovered that while the failure rate for reports with Reporting Services 2005 was directly correlated with HTTP 503 errors, the failure rate for reports with Reporting Services 2008 was directly correlated with time-out errors. These time-out errors, combined with our analysis of the disk queuing on the data source, indicate that the data source was unable to keep up with the request for data from the front-end servers.

Figure 12: Push Test – Detailed Error Analysis

Note: The chart lines for the Reporting Services 2005 tests in Figure 12 only include the errors for the user load before the number of errors/sec climbed to the point where they completely skewed the overall numbers.

Average Response Time

When we analyzed the average response time and total requests counter values from our push test with a mix of report renderers (see Figure 13 and Figure 14), we discovered that while the average response time with Reporting Services 2005 was better than with Reporting Services 2008 until Reporting Services 2005 was completely overwhelmed and unable to keep responding (at about 9,000 users), Reporting Services 2008 just kept on rendering reports for a much greater throughput. Based on the disk queuing counters that we observed on the Reporting Services data source, we believe that significantly greater response times could have been achieved with Reporting Services 2008 with more hardware capacity.

Figure 13: Push Test with Mix of Report Renderers—Average Response Time

Figure 14: Push Test with Mix of Report Renderers—Total Requests

Miscellaneous

During our testing, we also observed the following:

1. The Reporting Services 2005 best practice of using the file system for extreme loads was observed to have a negative impact on results and should be avoided in Reporting Services 2008. For more information on this setting in Reporting Services 2005, see Planning for Scalability and Performance with Reporting Services.

2. During our initial optimization of the default Reporting Services 2008 and Reporting Services 2005 configurations, the only change that we made was to preallocate space to the ReportServerTempDB database. This change had a significant beneficial impact on performance. We did not attempt to optimize the performance of this database by placing this database on a very fast drive or by spreading this database across multiple drives. From our analysis of our tests, we believe that the disk configuration of the ReportServer and ReportServerTempDB databases is even more important in Reporting Services 2008 than in Reporting Services 2005. Based on our tests, we believe that every Reporting Services 2008 installation or upgrade should pay close attention to the disk configuration of these databases. For additional information about optimizing disk configuration for Reporting Services 2005, see Planning for Scalability and Performance with Reporting Services. While the advice in this white paper is targeted to Reporting Services 2005, most or all of the advice is applicable to Reporting Services 2008. More testing is probably advised in this area.

3. At the end of our tests, we made some preliminary tests to determine if Windows Server 2008 would provide some benefit over Windows Server 2003. Among the improvements in Windows Server 2008 are improvements in memory management and in the TCP/IP stack. While there was some improvement of Reporting Services 2008 on Windows 2008 versus Reporting Services 2008 on Windows 2003, the data that we collected was inconclusive. We expect that the rendering of many larger reports may well benefit from the memory performance improvements in Windows Server 2008. In addition, subsequent to the build that we used for our tests, there is an improvement in Reporting Services 2008 related to the rendering of images (PDF, EMF/Print, TIFF) when Reporting Services 2008 is running on Windows Server 2008. Based on other testing, this has been shown to result in about a 5.7% performance benefit for image rendering. More testing is required to determine the benefit of Windows Server 2008 in conjunction with Reporting Services 2008.