SQL Server Reporting Services - Do you cache popular reports for better performance?
Do you cache or not?
By default SQL Server cache stores the data & query plan in cache to provide better performance for your queries. Similary caching is also included in SQL Server Reporting Services where this caching can shorten the time required to retrieve a report if the report is large or accessed frequently. Bear in mind that If that server is rebooted, all cached instances are reinstated when the Report Server Web service comes back online that means all the cached reports will be disappeared. They will add on to the cache whenever they are called as per the setting on the Report Manager.
Talk about large scale when you have a large number of users all trying to access the same reports, the performance of your report server can suffer dramatically. That means the Reporting Services server will have high Cpu and that will affect the SQL Server performance too. So on the aspect it is recommended to enable caching reports for a certain period of time can drastically reduce the load placed on the report server, leaving it ready to handle other requests. This leaves the report server ready to process other reports quicker as it is not busy processing the popular reports over and over again.
SQL Server has classed this caching as a performance-enhancement technique. The contents of the cache are volatile and can change as reports are added, replaced, or removed. If you require a more predictable caching strategy, you should create a report snapshot. There a 3 types of Report Execution Modes available in the SQL Server Reporting Services, such as Running Reports On Demand, Running Reports On Demand From Cache & Running Reports From Snapshots. Be aware that any one of these Execution properties must be set for each report individually.
Books Online states that :
In some cases, configuring an on-demand report to run as a snapshot can deactivate subscriptions. The following condition will cause a report server to deactivate existing subscriptions that were defined while the report was configured to run on demand:
- The report uses query parameters, and you select a specific value as the default parameter in order to meet the requirements for running the report as a snapshot.
- Existing subscriptions are configured to use parameter values that are different from the default parameter value you specified for the snapshot.
Moreoever ensure you have updated your local copy of Books online without failure that will guide to setup such properties without failure.