Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting Services Caching and Snapshots


By:   |   Last Updated: 2018-07-26   |   Comments   |   Related Tips: > Reporting Services Administration

Problem

SQL Server Reporting Services offers several options for maintaining past executions of reports. Both the cache and snapshot history options were established in many prior versions and have only been made better with time.  Surprisingly these options are somewhat unknown and used only sporadically and are often misunderstood. The two different methods also cause further confusion as some of the wording and the functionality is intermixed into both methods. So, what can I use SQL Server Reporting Services snapshots history and cache for?

Solution

Before we go much farther, it probably would be great to explain the hierarchy of ways to save prior executions of reports. The first of history is the caching of a reports execution. Similar to the cache on you PC; this method stores recent execution(s) of your report. So, if you ran a sales report for the Georgia region covering last month, when enabled, SSRS would cache that report execution. Thus, if the cache has not changed (more later on how this happens), the next person running that same report for those same parameters, would retrieve the report from the SSRS cache.  Please note this cache is different than what SQL Server is using for SQL Server itself. Snapshot history works in a similar but not the same way. A history snapshot is a specific point in time version of the report with specific parameters being set; many versions of the report can be saved in the snapshot history.  In either case, both of these functions are designed to save your report users time when running reports especially those reports whose executions take a significant amount of time. Let us take a look how to implement each of these methods.

To get started you will first need to install Reporting Services if not already installed.  You can use this tip from Rajendra Gupta to assist with installing the latest version of reporting services. SQL Server Reporting Services 2017 can currently be downloaded from here. Be sure you are getting the latest edition and SSRS.

SQL Server Reporting Services Report Cache

As previously mentioned, a report cached works similar to the cache used by other computing functions. In essence, it stores a copy report data and report structure for later executions of the report. One thing it does not store is the rendering details which allows you to still benefit from report caching without being tied to a specific render format (such as pdf, Excel, etc.). To setup a report to use the cache, you must go to the report you would like to turn on caching. Next, you will want to manage the report by going to eclipse (3 dot) to the right of the report and then selecting Manage.

Manage Report

Next go to Caching Window for the report, as shown below.

Cache Window

On the Caching properties window, shown above, several alternative methods of expiring and refreshing the cache for the report exist. Of course, the default option for a report is to “Always run the report with the most recent data”, which I call the NO Cache option.

The second option is the pure caching option which allows us to actually set up a Cached copy of the report. When this option is selected, the subsequent execution of the report must use the same parameters in order for the cached copy of the report to be used. Furthermore, the cache expiration schedule is defined.  Upon the cache expiration, the report will execute as if no cache copy existed (which will in turn create a new cached copied).

On the same cache properties window, we can define how often a cached version expires.  The cache expiration time can either be defined in n number of minutes or on a specific defined individual schedule or a shared schedule (that a SSRS system administrator has defined).

cache refresh plan

Again, as soon as a report cache expires, the report will need to be “run again” to reload the cache for future users.  So, for reports whose data only changes monthly, it may be beneficial to expire the report cache only once per month, for instance. Furthermore, not only can the expiration time frame be set for a report’s cache, but also a refresh plan can be established for a cached report. The plan creates a new cache which replaces the current cache version; a snapshot, discussed in the next section, can also be created as opposed to an individual cached report.

To initiate the Cache refresh plans, you will first need to click the apply button as shown below.

Refresh Plan

Upon clicking Apply, the Manage Refresh Plan link appears.

Manage Refresh Plans

From this window, a new cache refresh plan can be added, a new plan can be created by copying an old plan, a plan can be deleted, and finally, the plan as a whole can be Refreshed now.

new cache refresh plan

As illustrated next, the refresh plan must be named, a schedule, either shared or specific to the report, is selected (or created), and finally, the default parameters or user selected values are selected. Be sure the cache expiration time frame does interfere with the refresh schedule (for example, the cache expiration time expires every night at midnight while the cache refresh schedule runs daily at 11.45pm.  That sort of schedule would defeat the purpose of having the refresh plan).

cache refresh plan

As shown below, the refresh plan is now running at 02:00. 

cache plan result

SQL Server Reporting Services Snapshots

Whereas caching a report is done somewhat automatically, snapshots require more setup, but also provide additional flexibility. Snapshots are established in the same area as caching. Just click on the three dots to the right of the report name and the select Manage. Next you will want to select the Schedule and settings option.

main snapshot

The first setting to be set is whether to create the history snapshot, and if yes, then on either a shared or individualized schedule. Under the advanced options, we must specify how many copies of the snapshot should be retained. Either the system default values, a n number of snapshots, or retain all options can be selected.  The last option provides a way for saving the cache history into the report history.

snapshot schedule

Subsequently, we could go ahead and select the New history snapshot option.

new snapshot history

Upon clicking the New history snapshot button, an actual snapshot is created.  You will notice in the below screen print, I created three report snapshots.  In thinking through this process, please note these historical snapshots are generally accessed by navigating to the History Snapshots window.

snapshot view

The times shown in the above screen print are shown for each snapshot that has been saved.

One alternative to retrieving a historical snapshot is to actually use a render tag in the URL to retrieve a specific snapshot; tag is rs:Snapshot. Thus, the basic format is: http://locahost/ReportServer/MyReport&rs:Snapshot=YYYY-MM-DDThh:mm:ss. An example of such a rendering method is included below.

snapshot rendering

To run the report from the most current snapshot, you would actually select the Caching tab and then select the radio button next to: Always run this report against pre-generated snapshots. Upon selecting this option, you will then select the appropriate Cache snapshots options related to when this snapshot will be generated, including creating the cache snapshot now.

Snapshot Cache

Of course, with both cached reports and snapshots, there are certain instances that do not allow for a report to be saved either in a cached version or snapshot. If the report uses Windows Authentication or Prompts the user for credentials, it cannot be cached nor a snapshot saved.

Next Steps


Last Updated: 2018-07-26


get scripts

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools