SQL Server Reporting Services
Users demand high performance reporting along with real-time data so when the reports they use are slow or even worse not functional, you’re sure to hear about it. Because performance issues, especially in a distributed product such as SQL Server Reporting Services (SSRS), are inherently loosely defined and difficult to isolate, it’s often problematic to identify the bottlenecks or improve performance. I say often as it’s not impossible but being proactive instead of reactive is the best way to avoid any unwanted problems from occurring in the first place.
That said, let’s investigate a few areas that should always be at the forefront when designing reports and/or confronting performance related issues.
SSRS CPU Utilization
Unlike most web applications, SSRS performs a lot of in-memory data manipulation that can be quite lengthy. CPU performance is fairly critical to report processing, especially if graphic manipulation, charts, and complex report layouts are involved. Reports containing many expressions may also tax the CPU in order to generate and compile a temporary assembly; therefore having a robust server should be employed.
SSRS Memory/Paging
Since SSRS does a lot of in-memory data manipulation, the memory usage overhead is quite high, especially in Report Server 2000 and 2005, where the entire report is processed in-memory up front. SSRS 2008 is somewhat better in this regard as it works in a streamed manner. When under load, the memory pressure increases and you need to ensure that the Report Server is not paging excessively.
SSRS Disk Performance
Report Server 2008 may save some data structures to disk when memory pressure is high so having a fast storage area network (SAN) is essential.
Authentication Response Time
Confirm that there are no events regarding domain controller (DC) availability in the event log. In widely distributed networks, make sure SSRS is using a local DC and not one in another locale.
SQL Server Performance
Reporting Services relies on SQL Server for the catalog database. All data from the Data Source is read into the ReportServerTempDB before it is processed. This guarantees the Report Server a consistent copy of the data for processing the report. It also means that performance of the ReportServerTempDB database is extremely important so take note of that.
When in SharePoint Integrated mode, the performance of the SharePoint API’s, which rely on the SQL Server containing the SharePoint Catalog and Site Catalog databases, can seriously affect Report Server Performance. Calls to SharePoint API’s are made numerous times during report processing so if this mode is enabled, careful construction of the report(s) is necessary.
Data Source Performance
This may be SQL Server or some other data source (Oracle, ODBC, etc.) that is being referenced. You can see the amount of time for the main query in the ExecutionLogStorage table in the ReportServer catalog database. However, there are many other SQL requests that are not listed in this database, such as the overhead for parameter queries, parameter validation queries, the cost of retrieving data from secondary streams, etc. Having well performing SQL, stored procedures (SP’s) as well as the DB in the same locale/LAN segment will ease DS report linking.
Network Delays
Experience has shown that network overhead is typically negligible but still should be considered. In order to determine if there are any issues, client and server traces need to be taken and traffic analyzed to see if the time delta between calls as seen from the client side and server side show any significant degree of variance. Tools such as Fiddler and httpWatch do a good job of tracing the network call stack.
Report Design
Report Server does not have any intrinsic tuning settings other than setting maximum memory. Report design is a big factor in report performance. The following features add to the complexity of report rendering and the time it takes to render a report so paying close attention to how these features are being used will yield huge dividends in performance:
· Grouping
· Sorting
· Graphics
· Charts
· Sub reports
· Aggregation
· Query Performance
Parameter Validation
If parameters are derived from queries, the query will be run a second time when the user executes the report in order to validate the data. Using hard-coded parameter values will perform better but obviously has its drawbacks.
Aggregation
If possible, perform aggregation in the query and not within the report server.
Sorting and Grouping
Sorting the data retrieved from the data source to match the sorting and grouping levels in the data region will make for faster processing of the report (i.e. use an ORDER BY clause in the SQL/SP).
Browser
Use the most current browser, for instance - IE9. Older browsers typical have an inefficient layout engine compared to the latest browsers that are available. IE9 can improve the retrieval and performance of complex reports, especially if multiple reports are being used within a page.
Antivirus Software
Some antivirus (AV) software installs network filter drivers that can affect network performance so determine if your AV software may be causing an issue.
Move to SSRS 2008
SSRS 2008 is architected to provide better overall throughput under load than SSRS 2000 or 2005. In addition, large reports do not starve smaller requests. For larger reports, SSRS 2008 only processes what is necessary to render the page requested, whereas SSRS 2000 and 2005 process the entire report up-front, contributing to a longer time to render the first page. SSRS 2008 also performs better in certain page-to-page scenarios, as well.
Enable Kernel Mode Authentication
This has been shown to improve performance because of reduced context switching from kernel mode to user mode however for longer reports; this difference will be less noticeable.
Report History and Snapshot
For long-running reports, keeping a history or using background processing to generate a snapshot that interactive users can view, as opposed to re-running the entire report, can improve the user experience. Obviously, this is not feasible if the number of parameter choices are large or the data contained in these snapshots is enormous but it is something to consider.
Summary
With a little work from all the players on the team (developers, network administrators, business analysts, etc.) poorly performing reports will be a thing of the past.