Featured Database Articles

Report Caching in SQL Server Reporting Services 2008 R2

By Arshad Ali

Introduction

SQL Server Reporting Services (SSRS) is a server based scalable and extensible platform for authoring, deploying, executing and managing reports based on a variety of data sources. SSRS allows us to create interactive, tabular, graphical (using data visualization controls) or free form reports from relational, multidimensional (using MDX or DMX) or XML data sources. Furthermore it allows you to view/export your reports in a variety of formats. You can specify to create report snapshots, which show data at a point of time or you can even subscribe to your published reports.

To improve the performance of report processing, SSRS lets you enable caching for the report so that if the same report request comes again, the stored copy can be rendered in the desired format and served instead of processing it from scratch. In this article I am going to explore this caching feature in detail.

Understanding Report Caching

"Caching is a mechanism to process the request and keep the response stored somewhere so that if the next time the same request comes, the response can be served the from the saved instance than generating the response again. Serving the stored response improves the response time for all subsequent requests as long as cache is available. Cache gets invalidated after defined schedule or occurrences of some events."

SSRS lets you enable caching for the report, in which case SSRS will maintain (store in ReportServerTempDB database) a copy of the processed report (along with data) in intermediate format so that if the same report request comes again, the stored copy can be rendered in the desired format and served. This improvement in subsequent report processing can be evident especially in cases where the report is quite large and accessed frequently.

Please note, different combinations of parameters force SSRS to store a new cache and require more storage space for ReportServerTempDB database. Also the reports served from cache will consistently show the same data (even if the data has changed since the last query) until the cache is refreshed. A cached report can be set to expire after a specified time interval or after a specific date and time. After expiration, a cached report is replaced with a newer version when the user selects the report again.

Caching for a report can be enabled only if the report uses the data sources with stored credentials; in other words caching cannot be enabled for a report if it prompts users for credentials or uses Windows Integrated authentication.

Figure 1 - Stored Credential used for Caching

Getting Started with Report Caching

To enable caching for a report, go to Report Manager, navigate to the folder that contains the report, point to the report for which you want to enable caching, click on the down arrow and click on Manage menu item as shown below:

Figure 2 - Managing a report

On the manage report screen, you will see the Processing Options tab on the left side; click on it to open the page where you can set caching. On this page, you will see two top level options:

"Always run this report with the most recent data" - In this case report processing retrieves data from the data sources or if the cache is available it is returned to the user.

"Render this report from a report execution snapshot" - In this case the report is served from the report’s execution snapshot created on scheduled time. Report request is served from the report’s execution snapshot. A snapshot doesn’t expire but rather is replaced with newer version on the next scheduled execution.

Figure 3 - Specifying the Processing Options

Next you can specify whether you want to enable caching for the report. If yes then when you intend the cache to expire, you can either set it to expire after a specified number of minutes or after a defined schedule as shown below.

Figure 4 - Specifying a schedule to expire the cache

As I said before, a cache will be created once you enable caching for the report and the first request comes. You can run this query to verify if the cache has been created. You might see multiple cache copies depending on each unique combination of parameters.

Apart from enabling caching for a report, SSRS also allows you to specify a cache refresh policy. Cache Refresh policy creates a schedule for preloading the cache with the specified parameters values. To create a cache refresh policy, click on the Cache Refresh Options tab on the Manage page as shown below and next click on the New Cache Refresh Plan link.

Figure 6 - Specifying the Cache Refresh Plan

Cache Refresh policy page asks you to specify the schedule for preloading the cache along with the parameter values that will be used for generating the report cache as shown below:

Figure 7 - Specifying a schedule to refresh the cache

Please note, if you are modifying the report definition including parameters, changing the credentials or changing the report execution options, the report cache will get invalidated whereas if you are deleting a report, report cache will also be removed.

Data Driven Subscription with NULL Delivery Extension

A subscription is a report snapshot generated on some defined scheduled time and delivered to the intendant audience by SSRS. SSRS lets you create two types of subscription, i.e. Standard Subscription and Data Driven Subscription.

Standard subscription is created by any user having view reports permissions with static values. It means whatever parameters the report expects need to be provided at the time of subscription creation and cannot be changed at runtime. Whereas in the case of Data Driven Subscription (can be created by someone who has Content Manager Permission), data needs during execution of the report can come from a query (or a database) including recipient list, delivery method and parameter values needed for each recipient's report execution.

SSRS has provided two delivery extensions out of the box. The first one lets SSRS save the report subscription at file share whereas the second one sends the report in email to the specified recipients. There is one more extension; if you have SSRS installation in SharePoint Integrated mode, which delivers the report to a SharePoint library.

Now if you are wondering, why I am discussing subscription in a report caching article then I would say hold on for a moment. Data driven subscription has one more specialized delivery extension (called NULL delivery extension) that instead of sending reports to file share or email, preloads parameterized reports cache. NULL delivery extensions can be set up only in data driven subscription by an administrator to preload the cache or generate the report snapshots and hence improve the performance of report server by reducing the time it takes to present the reports to users.

Conclusion

In this article I talked about how SSRS improves the performance of report processing by allowing us to enable caching for the report so that if the same report request comes again or for all subsequent requests the stored copy can be rendered in the desired format and served instead of processing it from scratch, as long as report cache is available. Next I will discuss how you can set up a cache refresh policy to refresh/preload the cache automatically by SSRS with specified parameter values.

Advertiser Disclosure:
Some of the products that appear on this site are from companies from which QuinStreet receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. QuinStreet does not include all companies or all types of products available in the marketplace.