Populating the Cache before your users pound your report server

Often people take a look at their report execution times and wonder how to optimize it. They readily see that it is possible to cache a report the first time a user runs it so that subsequent users don't have to incur the initial execution time.

Well that still leaves the first person to take the 'pain' of waiting for the report to run. If yours is like many organizations, the 'first person' is actually a race right at 8am to see who clicks the report. Many folks end up waiting through that initial report execution time.

Luckily, there is a feature of reporting services that allows you to solve this and prepopulate the cache before your users show up to click the report. Before we get to the solution, a little background.

There are two kinds of report parameters – those that are used in a query (query parameters) and those that are not (report parameters). The distinction is purely logical and not perceived by the end user.

For reports that have only report parameters, priming the cache is straight forward – just run the report before your users try to access it for the first time (I’ll explain how to do that in a bit). For reports that have query parameters, priming the cache is more difficult – you need to run the report once for every parameter combination before your users try to access it for the first time.

Running the report once for each parameter combination can be difficult, but at least manageable. If you want to know which parameter combinations users use most often, you can check the report server execution log parameters column (read about how to get it here: https://msdn2.microsoft.com/en-us/library/ms159110.aspx). The alternate approach is to figure out all possible parameter combinations. Either way you do it, you’ll need this info for the solution to work.

Reporting Services supports data-driven subscriptions. These are subscriptions that determine to whom to send a report and what data to include in that report based on the results of a query. When a report is executed in the data-driven subscription and that report is set to execute from cache, the cache copy is potentially updated. If a copy of the report is already in cache for the parameter combination, then it is just retrieved from cache rather than re-executed.

So to populate your cache automatically, what you can do is create a data driven subscription. Use the query of the subscription to retrieve the parameter combinations you need. Then set the data-driven subscription to deliver the report to the ‘Null’ delivery extension and to render the report in using the ‘Null’ rendering extension. The ‘Null’ delivery and ‘Null’ rendering extensions are extensions we added to reporting services specifically so you could populate the cache in this way. The term Null is a play on the Dev Null concept in UNIX. When you use the two of these extensions together, the report will execute (queries are executes, expressions evaluated, etc.) but will not render and it will not be delivered anywhere either. The result will be the executed report will be saved in cache. Then set the schedule for the subscription to the time you need the report and enjoy the perceived faster report execution.

A final note: When report server runs a report, some parameters may have valid values determined at runtime based on a query, or the default value could be based on a query. When RS caches a report, it does not cache the results of these queries, the same is true for report snapshots. So even though you have cached the report execution, you may still see query load on your underlying database due to report parameter queries – plan accordingly.

Take care and good luck,

-Lukasz