One can enable caching for their reports in SSRS. When we do that, the data is then retrieved from the ReportServerTempDB instead of real time execution of the query. But there are things that you need to be aware of before you start caching the reports. The caches can only be expired based on elapsed time or a schedule i.e. if the underlying data has changed and a request is made to render the report and the elapsed time/schedule has not hit yet, the end users will get the cached data rather than the latest and greatest data from the database. This may or may not be an issue for your environment(s). Also, remember that in case the report has parameters, then a cached version of the report is created for each unique set of parameter values.
So, what happens when we enable caching and run the report? When it is run, then the ReportServerTempDB database is checked first to see if a cached version of the report exists and if it does, the data is then retrieved from the cache otherwise it is retrieved from the database and it is cached for future use. So, in many situations where the underlying data does not change that often (i.e. not within the elapsed time/schedule), and there are many users of the report, caching can really help to reduce the load on your reporting instance.
Let’s walk through the steps of enabling caching for a particular report. We will first need to start with the data source. Any report that needs to have the caching enabled needs to have their credentials stored on the report server.
The windows account that is being used should have all the necessary privileges in place for being able to render the data from the underlying database(s). Now, in order to enabling caching for a particular report, let’s open up the properties page for the report and then click on Execution – you will see something similar to this screen:
Over here, we can now choose to either:
a) Cache a temp copy of the report and expire it based on a schedule – default is 30 minutes. Or
b) Create a schedule which could be a shared schedule or a report specific schedule. When you click on the “configure” button for the specific schedule, the screen for the schedule details is pretty simple to follow and configure a schedule:
So, that is it. Using these simple steps, we can configure caching for a report in SSRS. And if you want to see what is going on from the DB side, you can always query the dbo.ChunkData, dbo.ExecutionCache and dbo.SnapshotData tables in the ReportServerTempDB database. We can join all three tables together using SnapshotDataID column. There are lots of very good articles on MSDN as well as in BOL which go over caching in great detail. You can see those in the resources section below.
Resources:
Systems Engineering and RDBMS 