Report Server Administration - Professional SQL Server 2005 Reporting Services - page 478

Remember that two database accesses occur with each report process: once to the Report Server catalog
for the rdl file content and then to the specified data source to retrieve a result set for use in the report.
Once those two are brought together, generation of the report can begin.

Both user-triggered and timer-triggered report execution share a common stage in their processing. This
stage occurs after the Report Server has gone to the report catalog to retrieve the rdl file and then gone
to the source database to retrieve a result set for the report query. When the data and the report file are
combined, ready to generate a rendered report, that's the moment when the report exists in prerender
limbo. The Report Server has the ability to cache this prerendered report snapshot. The snapshot itself is
static, in that any reports rendered from it will all be based on the same data result set. Rendering the
report to the user no longer requires round trips to the databases. That has potentially large implications
for increasing server scalability and performance.

Many performance counters are included with Reporting Services, including ones to monitor cache use.
For example, the “cache read miss” counter shows the number of times the server went to access data in
cache, but it wasn't there. One example of that is when the cached version has expired. Missing cache
data would cause the server to run the report process again, from initial catalog query to report render-
ing. You'll read more about using performance counters later in this chapter.

Reporting Services also performs session caching. User sessions are maintained between the client and
the server when exchanging HTTP messages. These are user-specific, and data is not shared across ses-
sions. When a report is viewed, the report is added to the session cache for that user. The
ReportServerTempDB database is used to hold this type of short-lived data. The user must refresh the
report in order to see changes in the data that might have occurred since the report was first requested
by that user. Refreshing the report is done using the Refresh button on the report toolbar in Report
Manager. Refreshing the browser does not force a report refresh — it only causes a reload of the cached
report.

It's important to know that cached instances are temporary and must expire . They expire:

❑ According to a predefined interval, like every 10 minutes.

❑ According to a report-specific or shared schedule.

❑ By forced expiration, such as when the ReportServerTempDB is restarted.

Reporting Services doesn't force a limit to the number of cached instances you can have, but your hard-
ware will certainly have its limits. SQL Server Agent handles scheduling duties for the report server and
also takes care of timing out items in cache. When planning your security policy, remember that cached
instances must use credentials that are stored on the server.

Exploring the Execution Log
One of the first ways to troubleshoot and monitor report execution is by going through the Report
Server Execution Log. When a report is executed, log information is written to the ReportServer
database. This info is helpful, for example, when you want to see how often a report is run or who is
requesting the report.

A number of attributes are recorded for each report execution. These include: