Last updated on July 26th, 2022 at 08:50 am
Some of the reporting questions that I get asked have to do with the burden reporting puts on servers and how this can be reduced or controlled. In general, the burden for most reports and organizations is minimal as reporting is generally an underused feature. However, if your company uses reporting a lot and you have concerns about the CPU and disk load on your SSRS server, then SSRS report caching is a great feature that will reduce both disk and CPU I/O on your SSRS server. This is where Report Caching coming into play!
Caching allows you to keep a copy of a previously run report and display it again without updating the results. This can be useful for reducing the load on your SSRS and SQL servers when you know that end users are likely to run a report over and over and over again.
Based on my experience, Software Update (SU) reports put the most load on your SQL and SSRS servers. This is particularly true when the report uses Role-Based Administration (RBA).
If a report takes several minutes to run, and it will be run a few times during the day, then SSRS report caching might be the solution for you.
How do you enable report caching for a SSRS report?
I will use SSRS 2016 in this example, but the steps are similar for other versions of SSRS.
Open your SSRS site and browse to the report folder. Locate the report you want to cache and click on, “…” (red arrow). Then click on the MANAGE button (purple arrow).
On the report Properties page, click on the Caching node.
Select the Cache copies of this report and use them when available radio button.
At this point you have to make a decision:
How long do you want to cache the report?
Do you want to cache the results for 30 minutes or for the whole day?
Within the screenshot below, you can see that results can be cached for “x” minutes, or you can setup a schedule. Both options have advantages and disadvantages, so the choice depends on your needs.
Once you decide, click Apply and from that point on the report will use cached results.
I recommend that you add the date and time as a field within your SSRS reports. This will show you exactly when the report was executed. Therefore you can tell if the results are from a cached version of the report. You will find that in the footer of all Enhansoft reports, the date and time of when the report was executed is noted.
Finally, don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, If you have any questions, please feel free to touch base @Garthmj.