Caching in Reporting Services

Reference

To create this article I have refered a book on reporting services by the name "SQL Server 2005 Reporting Services in Action". The following article is my understanding of the concept of caching in reporting services.

Report Caching

Report Server does processing in two stages:

• Execution
• Rendering.

During the execution stage, the Report Server retrieves the required report data, combines the resulting dataset with the report layout information, and generates the report’s intermediate format (IF). This report’s intermediate format can be cached for fast retrieval. Caching can be used based on the fact, report data does not change very often. Caching will make report processing more efficient. Report server supports three forms of caching:

• Report session caching
• Report execution caching
• Snapshot caching

Let us discuss about each one the methods closely.

Report Session Caching

Report caching cannot be turned off by the user. When a report is processed, the Report Server stored its IF in the ReportServerTempDB database and uses the cached copy until the report session expires. In report session caching, the Report Server caches the reports’ IF on a per client basis .i.e. one session serves only one client.

A report session is always associated with exactly one client. For one report session, the report server can serve subsequent report requests from the same client and for the same report from the cached report IF. Report session is used to ensure data consistency and improve performance. One of the main uses of report session caching is to ensure that the report data does not change within a given period of time. (.i.e. during the report session). Report session plays an important role in improving the performance when processing interactive reports. We cannot completely turn off report session caching, but we can specify the session expiration to suit our needs. The session timeout can be changed by changing the value of the SessionTimeout setting in the ConfigurationInfo table in the Report Server database. 60 seconds seems to be an ideal value. All caching techniques result in inconsistent data, but we must be careful to evaluate as to how much staleness is acceptable.

Report Execution Caching

Report execution caching is disabled by default. This feature can be enabled using the report execution page. This is very similar to report session but there is one major difference. The report session the reports intermediate format is actually mapped to clients accessing reports, but here, this is global. In this form of caching, several client applications may access the same cached instance of a given report.

If report execution is enabled, only one instance of the report’s IF is cached. All requests for the report will use that instance. The main aim of report execution is to improve performance. If the report does not have parameters, only one instance of the report will be cached. If the report is parameterized, then several instances of the report are cached, one for each set of parameters. There are a couple of conditions that must be met before report execution caching can be done.

• The report must not use Windows authentication in expressions or to connect to the database.
• The report should not prompt the user for database login credentials.

The following can be one of the reasons that cause the Report Server to move out of the cache.

• The Report Server application domain restarted
• The cache instance has expired
• The cache instance is explicitly invalidated by calling the RS Web service Flush-Cache method
• The report’s execution option changed
• Change in report definition file and data source

We can also force the Report Server to expire the report’s cached instance on a set schedule. This is useful when you want to ensure that the Report Server will process the report at a specified time. To select the report execution option, select properties from the report manager window, for the report where we want to enable caching.

Select the execution tab as shown below

Snapshot Caching

Snapshot caching refers to caching of report instances at a specific point by configuring the report for snapshot execution. The following are the advantages of snapshot caching.

• The performance is improved when the report is served from a cached copy in the Report Server database. This is useful for large reports that take long time to execute. Such reports can be scheduled to be generated during off-peak hours.
• It allows us to maintain a snapshot history log and compare different snapshot runs of the report.

When a report is configured to be executed as a snapshot, the report server saves the reports’ IF in the Report Configuration Database.

These are the features of Snapshot caching:

• Are usually executed in unattended mode.
• Refresh the report cached copy at a specific point of time.
• Require default parameter values in case of parameter driven reports.
• Are not interactive.
• Save the reports’ IF in the Report Server Configuration database.

The top half is for report execution caching and bottom half is for snapshot caching.