SQL Server 2008 Reporting Services Scalability

  • Article

Much has been said up to now about the scalability work the Reporting Services team has done in the SQL 2008 version.  However, until now, there were no numbers to back up the architecture discussions.  The SQL Server Customer Advisory Team (SQLCAT) just released a Technical Note titled Scaling Up Reporting Services 2008 vs. Reporting Services 2005: Lessons Learned.

This is a must read for anyone looking to compare the two products.  It also shows that an investment, even now, in SQL 2005 Reporting Services provides a roadmap to higher scalability in the future.  Previously, I discussed an approach for Scale Testing Reporting Services that is very similar to the approach used in the Technical Note. 

Some key notes 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)"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. "

The key take away from the Technical Note is that SQL 2008 Reporting Services can scale-up better than SQL 2005 Reporting Services.  This opens the door to hardware consolidation with maintained service quality.  In SQL 2005 Reporting Services, we provided guidance that scale-out should be pursued relatively quickly - after 4 CPU cores was reached. In contrast, SQL 2008 works quite well with 16 CPU cores.  We still recommend moving to a scale-out deployment topology for both the scalability and availability benefits that configuration offers.  However, each of the machines in the scale-out can have higher specifications that will translate to increased scalability.

At the back of the paper, in the Miscellaneous section, there is some advice that goes to show it pays to read all the way through, and I wanted to highlight:

3) "Based on our tests, we believe that every Reporting Services 2008 installation or upgrade should pay close attention to the disk configuration of [the Report Server] databases."

It is important to understand that SSRS 2008 uses the Report Server database heavily.  Now that memory utilization on the Report Server computer has been alleviated as a core scalability bottleneck , the other predominant limiters of scalability - CPU and I/O - are coming to prominence.  On the I/O side, there are specific things to note:

  • Database performance is key to making Reporting Services scale.  Investing in a good I/O subsystem underneath your Report Server database will yield significant benefits.
  • When SSRS 2008 reacts to memory pressure, it uses the local disk on the Report Server computer to cache objects and consequently reduces memory footprint.  This will result in increased disk I/O relative to SQL 2005 Reporting Services.   Administrators should start monitoring the I/O performance of their Report Server boxes and investing in faster locally attached storage when bottlenecks are identified.
  • Since the Report Server databases typically are located off box, administrators should monitor the network link between the Report Server and the Report Server databases to ensure there is sufficient bandwidth to efficiently handle the network I/O.

Take care and good luck,

-Lukasz