A few months ago, leading up to the release of RC0 of SQL Server 2008, I was involved in working with the SQL Server Customer Advisory Team. They were investigating and comparing the scalability of Reporting Services 2005 and Reporting Services 2008, using common customer report scenarios and workloads. A more detailed whitepaper is planned for the future, which will also provide the underlying code and test framework so that you could plug-in your own reports, define your workload, and run these tests on your particular hardware and environment.
In the meantime, if you are thinking of moving to Reporting Services 2008, I’d strongly encourage you to read the following technical note titled Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned.
The key take away from the Technical Note is that SQL 2008 Reporting Services can scale-up better than SQL 2005 Reporting Services. Summarizing the results, quoting from the technical note:
1) “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.”
2) “With our test workload, Reporting Services 2008 consistently outperformed Reporting Services 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.“
The SQL Server Customer Advisory Team did a great job of pulling together report scenarios and workloads, testing on three different hardware platforms, testing a few configuration options, and identifying key results. These results validate some of the quite significant work we have been doing in Reporting Services 2008. Furthermore, note that the scalability testing was performed with a non-optimized pre-release RC0 build of Reporting Services – an RTM deployment of Reporting Services 2008 is expected to show even slightly improved results.
The underlying architecture has changed considerably in Reporting Services 2008 (e.g. removing IIS dependency), and we added a number of quite significant new capabilities in the report processing engine and rendering extensions (e.g. tablix with arbitrary grouping and flexible layout support, vastly improved scalability support). We have been doing ongoing and extensive performance, throughput, and scalability testing internally within the Reporting Services team – so I guess some of these great results shown in the technical note weren’t all that surprising to members of the RS team. There was also anecdotal evidence and excitement by customers in blog postings and other forums, impressed with some of the improvements in Reporting Services that were visible as early as with the public SQL Server 2008 CTP release back in July 2007.
In future postings on this blog, I plan to cover important tips and tricks related to diagnosing and improving performance of complex reports (e.g. involving custom code) in RS 2008. This will hopefully enable you to even better take advantage of some of the underlying architecture changes in the RS 2008 report processing engine, such as dynamic on-demand processing.