Speed up reports in SQL Server Reporting Services with caching

Reporting applications tend to be slow, as reports have to process large amounts of data before they can display results. The built-in report caching feature in SQL Server Reporting Services (SSRS) feature helps speed up report execution and lowers the load on the reporting server.

When designing a report, it is important to keep in mind how much data might be processed. This is often affected by what parameters the user can select as well as which ones they are most likely to select when viewing reports.

When a report is run, SSRS first runs the database queries in the report and then renders the report in the specified format.

If report caching is on, however, SSRS stores the report layout and datasets returned from the database the first time the report is executed. Therefore, when the report is re-executed, a cached copy exists and the report only needs to be rendered from cache to a device specific format.

The main benefit is SSRS does not need to make database calls to the data sources if there is a cached report. Query execution is the most time consuming part for many reports and with report caching, report run time may be reduced from several minutes to a second or two.

Using report caching

The report caching feature is off by default. To turn it on, go to the Properties tab in the Execution page in Report Manager, as shown below:

Report caching can be turned on for a specific number of minutes or it can expire based on a schedule.


The first option is suitable for reports that could benefit from caching, but have data that changes often enough that you may want to refresh with certain frequency. The frequency is report specific and can range from minutes to days.

The other expiration option is either a report-specific schedule or a shared schedule. For example, if a data warehouse is loaded nightly between midnight and 5 a.m., a shared schedule could be created for 5 a.m. daily, and the cache could be configured to expire on that schedule. Users would benefit from caching refreshed with new data every morning.

It is important to understand that since reports often have parameters, and the data that comes back from the database differs based on their values, SSRS has to cache a copy of the report for each unique combination of parameters.

For example, if you have a Year and Month parameter and you execute January 2009, the first report execution will be slow but the subsequent executions will be fast. When you select February 2009, the report has to run database queries again and it might run for a while. But once the report is cached, subsequent runs will be fast again.

The cache is report-specific, meaning any user that executes a combination of parameters for the first time will cache the report for all users. This is one of the reasons why a report can only be cached if the data source uses stored credentials -- either a SQL Server login or a Windows account.

Report caching is most useful for reports with no parameters, or reports with only a few parameters with a small number of possible combinations. It is less beneficial for reports that have datetime parameters, which allows users to select one or more arbitrary dates. The best option for mitigating this issue is to provide default values to parameters with the most likely values pre-selected. Another option is to write a report rendering application and, using the SOAP API, render all possible parameter combinations to place reports into cache.

Report snapshot

In addition to caching when the report is executed for the first time, you can also set up the report to be rendered from a report snapshot (See the bottom section in Figure 1 above).

Once again, you can set up either a report specific schedule or a shared schedule. You also have the option of generating a snapshot when you click the Apply button, although that is likely practical only for highly-static reports. When the scheduled time comes, SSRS will execute the report and store the results.

The difference between snapshots and caching is that snapshots are generated by SSRS based on a schedule while cached copies are created the first time a report is rendered by a user or by an external application.

Snapshots eliminate the "waiting penalty" for the first report user, but they do have a major limitation: A report can only be configured for snapshot execution if it has no parameters, or if all parameters have default values.

When a snapshot is rendered, the default parameters are used. When the report is viewed, Report Manager will display the parameter values of the rendered snapshot. It also disables the parameters, however, and you cannot change their values. In other words, if the report has parameters and snapshots are enabled, the ability to run the report with more than the default parameter values is lost.

Therefore, report snapshots only work well if the report has no parameters, or if it's acceptable to show the report with only default values for a period of time.