Caching SSRS Reports

Caching SSRS Reports

In this article, I will show you how to use caching for SSRS reports. But before that go through below lines which will help to understand the concept of caching in sql server reporting services.

A cached report is a saved copy of a processed report. Cached reports are used to improve performance by reducing the number of processing requests to the report processor and by reducing the time required to retrieve large reports. They have a mandatory expiration period,

A report server can cache a copy of a processed report and return that copy when a user opens the report. To a user, the only evidence available to indicate the report is a cached copy is the date and time that the report ran. If the date or time is not current and the report is not a snapshot, the report was retrieved from cache.

Caching can shorten the time required to retrieve a report if the report is large or accessed frequently. If the server is rebooted, all cached instances are reinstated when the Report Server Web service comes back online.

Caching is a performance-enhancement technique. The contents of the cache are volatile and can change as reports are added, replaced, or removed.

To enable caching for a report, go through below steps :

1. First of all open Internet Explorer and go to Report Manager URL which is something  like below:

http://bhushan-pc/Reports2012

Your internet explorer tab looks like below :

1-Caching SSRS Reports

2. Click on your SSRS project. In my case it is Start SSRS. So now it will show you list of reports which are deployed on your report server.

2-Caching SSRS Reports

3. Now click on down arrow on the report which you want to subscribe and select Manage as shown in below screen shot.

3-Caching SSRS Reports

4. Then select Processing Options from left pane and you will see screen shown in below screen shot. In that screen, you have three different choice.

Either you can set time in minutes

4-Caching SSRS Reports

or you can defined a schedule as shown below.

4-Caching SSRS Reports-1

 

5. Then click on Apply button. So we have done with Caching SSRS Reports.

Main purpose of Caching SSRS Reports is to improve the performance. To see the effect of Caching, execute following query.

SELECT	ITEMPATH,USERNAME,PARAMETERS,TIMESTART,TIMEEND,TIMEDATARETRIEVAL,
		TIMEPROCESSING,TIMERENDERING,SOURCE,BYTECOUNT,[ROWCOUNT]
FROM	EXECUTIONLOG3

5-Caching SSRS Reports

Here you can see that it will not take even 1 second to retrieve data. Similarly you can see time difference in TIMESTART & TIMEEND.

6. You can also create Cache Refresh Plan which create a schedule for preloading the cache with temporary copies of data for a report. A refresh plan includes a schedule and the option to specify or override values for parameters.

To create a Cache Refresh Plan go to Cache Refresh Options and click on New Cache Refresh Plan.

6-Caching SSRS Reports

7. In that screen give details as shown in below screen. Then click on OK button.

7-Caching SSRS Reports

you can also check the history of Cache Refresh Plan.

7-Caching SSRS Reports-1

Congratulations! We successfully completed use of Caching SSRS Reports.

 

One thought on “Caching SSRS Reports

Leave a Reply to Pooja Cancel reply

Your email address will not be published. Required fields are marked *