How To Enable Caching in SQL Server Reporting Services

By:   |   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:

data source

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:

report folder

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

report properties

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

cache report

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:

schedule

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:

report layout

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.:

render report

The full toolbar is shown below:

report toolbar

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:

reportservertempdb results

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.



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley 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


Comments For This Article




Friday, September 6, 2013 - 8:07:10 AM - Ray Barley Back To Top (26645)

Run the query or stored procedure that the shared dataset uses in sql server management studio and you should see an error message that you can resolve

Friday, September 6, 2013 - 2:33:18 AM - Sunil Kumar Back To Top (26639)

Hi All,

We re running a SSRS Report on production environment and its taking too long time to execute and the output of the report is throwing runtime error (shown below):

 

An error has occurred during report processing. (rsProcessingAborted) The execution failed for the shared data set 'GetServiceBreakUsage'. (rsDataSetExecutionError)
Cannot create a connection to data source ' Data source for shared dataset'. (rsErrorOpeningConnection)

 

Please help me on the above one and we are workiing on it to fix as soon as possible.

if anybody has solution, Could you please drop me mail at: [email protected] or just send a reply to this post.

 

Thanks

Sunil

Wednesday, July 17, 2013 - 10:10:12 AM - Raymond Barley Back To Top (25880)

This is how it works in SharePoint 2010 (I haven't looked at 2013 yet).  Select 

a report from a document library and open the dropdown menu.  You will see a menu item

Manage Processing Options.  This allows you to set configure caching

Wednesday, July 17, 2013 - 5:59:41 AM - Akash Back To Top (25873)

Is caching of ssrs report possible when it is deployed on sharepoint server. If possible please o mention how to acheive.

Please help

Thursday, May 16, 2013 - 8:44:09 AM - Raymond Barley Back To Top (23984)

I'm using SQL 2012 Reporting Services; navigate to the folder that has the report you want to configure, hover over a report and click the dropdown.  Select Manage from the dropdown menu then Processing Options link.

Thursday, May 16, 2013 - 6:29:17 AM - gautam Back To Top (23978)

There is no execution property for a report in sql server report manager. I am using sql server reporting services 208.

Also history property not seen.

Please help

Friday, June 8, 2012 - 11:43:35 AM - Ray Barley Back To Top (17886)

You may be able to leverage report snapshots and have the parameters be applied as filters in the report.  In other words the dataset would contain more data than you need and you use filters on the data regions.  This supports caching better and could even work with report snapshots.

 

Here's a good tip for the filtering: http://www.mssqltips.com/sqlservertip/2597/dataset-and-tablix-filtering-in-sql-server-reporting-services/

 

Friday, June 8, 2012 - 8:04:51 AM - Amit Srivastava Back To Top (17882)

Hi ray,

I dont think this is best practice to opt for as user can exeute report for any parameter value, may b for today, yesterday, a week ago, a year ago or any day,

Case become worse when report has seven parameters in it.

Is there is any other other work around for the same as this trick is not good for reports having parameters

Friday, June 8, 2012 - 7:14:46 AM - Ray Barley Back To Top (17880)

Yes - you have to get the report to run with the parameter values the user will enter before the user enters it.  For example I had a project where the data warehouse was updated once a week.  The morning after the update about 150 people come in and start running reports; they want to know their sales for the previous week and how much money they made.  

You can create a data driven subscription to force the report to run with as many different sets of parameters as you want.  This forces the report to get cached before the user actually requests it the first time.

 

Friday, June 8, 2012 - 3:47:10 AM - Amit Srivastava Back To Top (17877)

I have implemented the same logic to the reports having parameter(User suppose to provide inputs) , things are good but from second attempt onwards for one set of parameter

For E.g.  If user select say date, 07/06/2012, it will take 20 sec to execute..and if again user select 07/06/2012, it will take a less than sec to complete...things are good till here..

But when User selects another date say 08/06/2012, again it took 20-22 sec to complete, again if 08/06/2012 is selected , will took less than sec.

I can understand, for each unique run, ssrs cache the data,

Is there is any way that will take time at first run for selected paramter but then after it will not take time of any selected parameter.?

For E,g, If user Selects 07/06/2012 first time, will took 20 sec to complete, but if after that user selects 07/06/2012 or 08/06/2012 or any date, will took less than a sec to complete.

 

Is there any way to do so...

Thursday, June 7, 2012 - 10:30:56 AM - Ray Barley Back To Top (17846)

Yes -  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

Thursday, June 7, 2012 - 4:35:19 AM - Amit Srivastava Back To Top (17824)

 

Hi Author,

My report has parameter of Date which user can change to fetch the data, is there is any way to use the cache property in report so that execution time has been reduced.

Wednesday, May 2, 2012 - 8:13:24 AM - Ray Barley Back To Top (17231)

The only way I know to add a report to the cache is to run the report.  Take a look at this thread: http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/195f9305-ff24-4d83-8935-50e2a3bdb913

 

Tuesday, May 1, 2012 - 2:28:21 PM - Jason Williams Back To Top (17223)

Is there way to perfrom this from the RS.exe with an input file.  I saw your posting on how to remove a report from the cache.

I would like to script adding a report to the cache using rs.ese with an input file.

Can you share how to do this?

 

Thank you, Jason

 

 

Tuesday, March 6, 2012 - 12:01:32 PM - Ray Barley Back To Top (16266)

A couple of other things to check from http://msdn.microsoft.com/en-us/library/ms155927.aspx

Not all reports can be cached. If a report includes user-dependent data, prompts users for credentials, or uses Windows Authentication, it cannot be cached.

A cached report is invalidated in response to the following events: the report definition is modified, report parameters are modified, data source credentials change, or report execution options change. If you delete a report that is stored in the cache, the cached version is also deleted.

If a report cannot be rendered from a cached instance for any reason (for example, if the parameter values that a user specifies are different from those used to produce the cached report), the report server reruns the report.

 

Tuesday, March 6, 2012 - 11:51:38 AM - Ray Barley Back To Top (16265)

You can try querying the report server execution log (see http://msdn.microsoft.com/en-us/library/ms159110(SQL.90).aspx) to see if there's something peculiar going on.

Tuesday, March 6, 2012 - 11:49:42 AM - Ray Barley Back To Top (16264)

You can definitely cache a linked report.  I did a quick test where I created a report and set it to render from cache with the cache expiring after 2 minutes.  Then I created a linked report.  I can see both the base report and the linked report being rendered from cache although there are two cached reports; the base is cached and the linked is cached separately; i.e. the caching treats them as 2 reports.

I'm using SQL 2012 RC0 but I think this behavior is the same in prior versions.

Monday, March 5, 2012 - 10:41:53 AM - Michal Back To Top (16249)

Hi,

I try to create caching for linked report. "Based" report is cached correctly, results appear quickly and 'Execiution time' is constant (caching time) but linked report doesn't work properly. Any time 'Execiution time' is new and it takes a long time. What can be wrong? Can I cache linked report?

br

Monday, February 20, 2012 - 7:23:08 AM - Ray Barley Back To Top (16092)

Caching and snapshots are the two most likely solutions.

Another option would be to use a subscription to generate the report and save it in a particular format like PDF, Excel, etc.  You can email the report or store it on a file share.  In this case the user simply "opens" the file and it should be quicker than what you've experienced with caching or snapshot.  However, you have to make sure that the features in your report are all supported in the output format that you choose.

 

Monday, February 20, 2012 - 4:42:13 AM - Namnami Back To Top (16088)

Hi,

I want to cut off run time so I tried rendering the report from cache. But this takes even more time than rerunning the original report!

 I tried setting it to render from snapshot and this takes only a bit less time. (40 seconds instead of 70 seconds). Isn't there a way to keep a copy of the report so when the manager clicks it he'll get it immediately?

Also, for my report I have a document map. All worked well till at some point I saw each time I navigate by the document map the report reruns!! This takes much time!

 

(report contains several sub-reports, does not contain rectangles)

 

Your advice appreciated, thanks

 

Sunday, February 12, 2012 - 9:48:27 AM - Ray Barley Back To Top (15995)

At the end of the tip you will see these queries:

 

select ReportID, AbsoluteExpiration, SnapshotDataID 

from ReportServerTempDB.dbo.ExecutionCache

 

select SnapshotDataID, CreatedDate, ExpirationDate 

from ReportServerTempDB.dbo.SnapshotData

 

select SnapshotDataID, ChunkName, ChunkType 

from ReportServerTempDB.dbo.ChunkData

 

Add the QueryParams and EffectiveParams to the SnapshotData query

and see if that provides any insight into what is going on.

 

Sunday, February 12, 2012 - 2:39:38 AM - Namnami Back To Top (15994)

Thanks for your reply.

I already have table filter parameters (which are not mentioned in the data query) but when I change them the query reruns.

I suppose (?) this happens because I also have other parameters which are query parameters. But even though I don't change the query parameters (which the cache was by) after I only change the filter parameters, the report requeries. I would have wanted the report to requery only if a query parameter is different then the cashe, not if only the table filter parameter changed.

Any Idea?

Also, I can't find how to place a filter on dataset in SSRS 2005 (my current filter is on the table itself).

Regarding the error log, I've already looked there, the error is AdjustTokenPrivileges failed , but I couldn't find help on that. Complete Error log (hope you don't mind I'm pasting this):

02/12/12 09:25:01, ERROR , SQLDUMPER_UNKNOWN_APP.EXE, AdjustTokenPrivileges () failed (00000514)

02/12/12 09:25:01, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied

02/12/12 09:25:01, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ProcessID = 7412

02/12/12 09:25:01, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ThreadId = 0

02/12/12 09:25:01, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     Flags = 0x0

02/12/12 09:25:01, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     MiniDumpFlags = 0x0

02/12/12 09:25:01, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     SqlInfoPtr = 0x075A5860

02/12/12 09:25:01, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     DumpDir = <NULL>

02/12/12 09:25:01, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ExceptionRecordPtr = 0x00000000

02/12/12 09:25:01, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ContextPtr = 0x00000000

02/12/12 09:25:01, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ExtraFile = <NULL>

02/12/12 09:25:01, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     InstanceName = <NULL>

02/12/12 09:25:01, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ServiceName = <NULL>

02/12/12 09:25:01, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used

02/12/12 09:25:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used

02/12/12 09:25:14, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\LogFiles\SQLDmpr0017.mdmp

02/12/12 09:25:14, ACTION,                  w3wp.exe, Watson Invoke: No

 

Thank you for your advice.

 

Thursday, February 9, 2012 - 6:13:46 AM - Ray Barley Back To Top (15949)

As far as your error you will have to take a look at the SSRS log files; e.g. I have SQL Server 2008 R2 and mylog file is at C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles.

As far as your question, caching stores a copy of the data ifor your report n the report server temp db based on the actual parameters.  Retrieving data from the cache is determined based on the parameters matching.  The only way around having the report cached for different parameters is if you don't use the parameters in the dataset query; i.e. you can use the parameters to filter the dataset after the query has executed.  In this case the cache may include more data than you need but you don't have to rerun the query; you're getting the data from the cache.

Take a look at this tip for an example of how to filter the dataset: http://www.mssqltips.com/sqlservertip/2597/dataset-and-tablix-filtering-in-sql-server-reporting-services/

 

Thursday, February 9, 2012 - 3:27:38 AM - Namnami Back To Top (15948)

Thanks this is really interesting.

I have an SSRS report with parameters (query paramters + filter parameters), I  undertand from what you wrote that a seperate cash is made for each run having different parameters: "if a cached report has parameters, a cached version of the report will be created and used for each unique set of parameter values".

Isn't there any other way to change an SSRS report table filter parameter and not rerun the report? I thought I could use cashing for this but I see the report reruns for every change of parameter (unless that paramter has been run and saved in cash already).

Also, as you explained I defined my report with cash expiry after 180 minutes, but every time I run it with the cashed parameters I get error:

  • An error has occurred during report processing.
  • An internal error occurred on the report server. See the error log for more details. - your help appreciated!
  •  














get free sql tips
agree to terms