Managing the Report Content Using Report Manager: Caching
In the Managing
the Report Content Using Report Manager: Part I
we saw how to go about setting the security for the Report Manager report
items. In this article we will be focusing on caching SSRS reports.
Caching is when a copy of the processed report
after the execution is saved into into ReportServerTempDB for faster retrieval.
This is beneficial when a particular report takes a long time to create and is
accessed multiple times. In this scenario if the report is cached then the
execution time is drastically reduced as the report’s result set is already
stored in the cache and is simply returned.
Since we will be using the ReportServerTempDB for
caching, it is to look at the difference between the ReportServer and
ReportServer Database Vs ReportServerTempDB
1. Stores information about the report schema, report property, data
sources, parameters, stores the folder hierarchy, report Execution log.
2. Always exists until changes are made to the RDL Schema
3. Helps to access the structure.
4. Data always exists during SSRS service restarts
1. Stores only the cached copy of the report data.
2. Expires based Expiry settings.
3. Helps to improve the performance of report execution as it is loading
data from cache.
4. Services restarts will clear the Temp Data
Note: You need to have
a data source with stored credentials on the report server in order to implement
caching. If you have windows credentials then it will throw the below error:
to Implement Caching in SSRS?
Implementing the caching in the SSRS is really very
simple. If you are using SSRS 2008 R2 then you need to just click on the report
on the dropdown for which the caching needs to be performed and click manage:
The following menu bar will be displayed on the
left pane from which we are going to focus on caching options.
the processing option. It will show a list of options which are as follows:
not cache temporary copies of the report.
the temporary copy of report for limited amount of time (only in mins).
can schedule the cached report to be available in the system for a specific
duration after which it will automatically expire
the report from the report history snapshot.
may use the system’s default setting (which is defined in web.config file). This is
applied to reports deployed on the report server. If you wish to change it you
may edit the web.confg file.
may never timeout.
may specify the report time out (only in mins) for this specific report only.
apply to save your settings.
How to implement caching
in reporting services?
a production scenario where a particular MIS report takes a long time to process and may even time out. Obviously there is a huge performance
issue with this kind of report. In this scenario you can either fine tune the
query or you can cache the report for faster retrieval. The below query takes more than 1 min for execution which
may be unacceptable in the live production scenario. I have purposely applied a
cross join to demonstrate the caching feature of ssrs.
SELECT pc.EnglishProductCategoryName, psc.EnglishProductSubcategoryName, p.EnglishProductName, frs.UnitPrice, frs.ProductStandardCost, frs.TotalProductCost, frs.SalesAmount FROM DimProductSubcategory AS psc inner JOIN DimProduct AS p on psc.ProductSubcategoryKey=p.ProductSubcategoryKey inner JOIN DimProductCategory AS pc on pc.ProductCategoryKey=psc.ProductCategoryKey cross JOIN FactReSellerSales
order to perform caching for limited number of minutes follow the below steps:
Click on the Product Sales Analysis report. Go to Manage->Processing
have selected the default option of 30 mins but as you can see its a free text cell
and you can input any number of minutes you want the report to be cached
for. Click Apply.
first as you can see the the report takes the same amount of time for execution
as the default running time of the query as the reason being the report is also
being cached while its being processed.
you run the report for second time the report executes much faster than the
previous instance obivously because this time it is being rendered from the
you can see we have an option of scheduling the caching duration which will
expire after the specific amount of time. You can schedule the report anywhere from
hourly to monthly basis. Also the option of the ‘once’ is given if the report
needs to be processed and expired on a specific day.
also have an option to render the report from the snapshot option for which you
need to specfy the schedule as when this snapshot needs to be taken. A snapshot never expires but is replaced with
newer version of the report on the next scheduled execution.
can schedule a cache refresh for my report on the schedule of hourly,daily
weekly or monthly basis. A cache refresh plan can help you preload the data on
the scheduled basis and ensure fresh data is being served.
the cache refresh plan is a scheduling operation ensure that SQL
Server Agent is up and running.
9. In this demo I have kept the
default settings but you can customize it as per your requirements.You can also
have a shared schedule in place and use the same caching throughout the
10. You can also
specify caching on parameters defined in you dataset and thus a new report will be cached
for different sets of parameters. Since I have not defined parameters the option
is unavailable for me.
11. After the cache
refresh plan has been added your screen will look something like this:
can see you may have an option of creating the new plan from the exisiting one
in which all the existing plans’ settings will be made available to you
or you can always create a new cache refresh plan.
In the next article we will see how to
manage subscriptions and scheduling of the reports to be delivered into your