Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

Reporting Services, the Need for Speed

As I have mentioned before Reporting Services (SSRS) in SQL Server 2008 has been completely re-architected, but how does that affect its performance.

Firstly it’s important to understand what what reporting services is – a web service to render and deliver data in a structured manner either in tables or as graphs charts etc.  So the first thing to note is that the speed of the report depends on the speed at which this service can get the data from the source.

Once the data is returned the rendering can begin.  In previous versions of SQL Server SSRS would render the whole report before displaying it to the user, and so that could take some time.  In SSRS 2008 the engine tries to work on the first page asap, although you will appreciate that all of the data will still be needed to cope with aggregations and pagination. It is also much more parallel and pipelined, so the users will see bigger performance gains on long and complex reports.  Another niggle was that some rendering outputs (e.g. excel) took longer than others and this has also been fixed in SSRS2008.

But how fast does SSRS need to be?  I think 20 seconds is a reasonable target especially for a long report because for me reporting is not an interactive task in that I don’t run report after report to get to the information I need. Taking this further many reports are required to meet deadlines for meetings or review which are known well in advance allowing them to be scheduled (provided you allow them to do that) which can further alleviate pressure on the server during the working day.  In fact the only time I really care about how long a report takes is when I design it and so it makes good sense to have some test data for that in a different server/database.

So then the question becomes how many users can an instance of SSRS support and for this the SQL Customer Advisory Team (SQLCAT) have done some work here.  There is a vital section at the end of this post abut configuring the database used for caching and collating data during the rendering process (normally called reportservertempdb) and a note to the effect that Windows server 2008 (as opposed to Windows Server 2003) will have a further beneficial effect.