How To Enable Caching in SSRS

By: Ray Barley | Updated: 2010-01-11 | Comments (24) | Related: > Reporting Services Configuration

Problem

We have many SQL Server Reporting Services (SSRS) reports that use our OLTP systems as data sources. We are always adding new reports and the number of users running these reports is also increasing rapidly. We would like to look at enabling report caching as a way to reduce some of the load on our database servers. In this tip I will go over the steps needed to enable report caching for SQL Server Reporting Services reports.

Solution

SSRS has a built-in caching capability where the data required to render a report can be retrieved from the ReportServerTempDB database instead of executing queries on the database specified in the report's data source. Caching is enabled in the Execution Properties for the report in the Report Manager. You can expire the cache based on elapsed time or a schedule. When a user runs a report enabled for caching, the ReportServerTempDB database is checked to see if a cached version exists; if it does the data is retrieved from the cache, otherwise the data is retrieved from the data source and cached for future use. Depending on the number of users running reports and how often they run reports, caching can reduce the load on your database servers by not running queries when a cached report is requested.

One point to emphasize about report caching is that the cache expiration is not triggered by changes to the underlying data; it is based on the expiration option you choose, either number of minutes or a schedule. The tradeoff with caching is that as soon as a new cached version of a report is created, the underlying data could change and the changes will not be reflected in a cached report until the cache expires.

In this tip we will walk through the following steps to show how to enable caching on a report:

Configure a data source
Configure caching for a report
Execute a report with caching enabled

Configuring a Data Source

In order to enable caching on a report, any data source used by the report must store the credentials for connecting to the database in the data source. As an example we will take a look at a SQL Server data source. Navigate to the folder in the Report Manager where your data source is deployed, and fill in the "Connect using" part of the page as shown below:

Note the radio button "Credentials stored securely in the report server" is selected, a user name and password are specified, and the "Use as Windows credentials" checkbox is selected. Whatever user name you choose must have at least read permissions on the underlying database; i.e. add the user as a member of the db_datareader database role for the particular database. In addition the user must have execute permission on any stored procedures that are used by the report; i.e. GRANT EXECUTE ON OBJECT::dbo.[stored procedure name goes here] TO PUBLIC (since the stored procedure only reads data you may want to give everyone access rather than just specific users).

Configuring Caching for a Report

You configure caching at the individual report level. Navigate to the folder containing your report in the Report Manager. Click the Show Details button to enabled the detailed view as shown below:

Click the icon in the Edit column for the report you want to enable caching. The Properties page will be displayed as shown below:

Click on the Execution hyperlink to display the Execution Properties page for the report. Fill in the page as shown below:

There are two options for enabling caching; they differ in how the cached version of the report is expired. The number of minutes option means that the cached report expires after the number of minutes specified has elapsed. When a report is requested and a cached version does not exist (or is expired), a new cached version of the report is created and it will expire in the number of minutes specified.

The schedule expiration option allows you to specify when the cached version of a report expires by utilizing a schedule. A Shared Schedule can be created in the Report Manager by clicking Site Settings on the Home page then Manage shared schedules. You can create a shared schedule then specify it for multiple reports. Alternatively you can create report-specific schedules. In either case the schedule looks like this:

Any report that specifies the above schedule for its cache expiration will expire every 15 minutes.

Execute a Cached Report

Let's take a look at the layout of the sample report that we will use to demonstrate caching:

The layout is intentionally simple; the textbox with the expression "Report Timestamp: " + Now provides a simple way to determine whether the report was rendered from the cache. When we initially render the report, we will see the current date and time. When we refresh the report, the current date and time will not change when the report is rendered from cache; e.g.:

The full toolbar is shown below:

The Refresh icon is located near the far right on the toolbar, between Export and the Print icon. We configured the cache to expire in 1 minute. Each time we click the Refresh icon the report will be rendered from cache until the cache expires. After a minute elapses we will see the Report Timestamp change, indicating that the report was not rendered from cache.

Let's take this a step further. We can query the ReportServerTempDB database and actually see report caching in action. After running the report, run this query:

select ReportID, AbsoluteExpiration, SnapshotDataID 
from ReportServerTempDB.dbo.ExecutionCache
select SnapshotDataID, CreatedDate, ExpirationDate 
from ReportServerTempDB.dbo.SnapshotData
select SnapshotDataID, ChunkName, ChunkType 
from ReportServerTempDB.dbo.ChunkData

Here is a sample of the output:

The following are the main points about the above results:

The ExecutionCache table has a row for each cached version of a report showing the expiration and SnapshotDataID
The SnapshotData table is linked to the ExecutionCache table via the SnapshotDataID column
The ChunkData table has the data and is also linked via the SnapshotDataID column
The purpose of this query was just to get a peek at what's going on in the ReportServerTempDB database to validate that caching is actually working

One final point about caching is that if a cached report has parameters, a cached version of the report will be created and used for each unique set of parameter values. The SnapshotData table has columns named QueryParams and EffectiveParams that handle this (not shown in the above result set).

Next Steps

The caching feature in SSRS is the best kind; it's just a configuration setting on a deployed report. Keep this in mind when you're trying to increase performance on your database servers. Enabling report caching can help. Each time you render a report from cache you didn't have to execute a query on the underlying data source used in the report.
Caching is not without cost; the ReportServerTempDB database will grow as necessary to store the data for the duration of the cache lifetime.
The cache expiration is pretty simple - specify how many minutes until expiration or a schedule. If you need to manually expire a cached report, take a look at our earlier tip How To Remove a SQL Server Reporting Services (SSRS) Report from Cache Using RS.EXE
You can download the sample report and SQL scripts to create the test data here and use it as a basis for your own testing and experimentation.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2010-01-11

How To Enable Caching in SQL Server Reporting Services