Report Execution Caching I: SQL Server Management Studio Perspective

About the Series …

This
article is a member of the series MSSQL Server Reporting Services.
The series is designed to introduce MSSQL Server Reporting Services (“Reporting
Services”), presenting an overview of its features, with tips and
techniques for real-world use. For more information on the series in general,
please see my initial Database
Journal
article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools
needed to complete the hands-on portion of this article, see BlackBelt
Administration: Linked Reports in Report Manager, another article within this
series.

Introduction

In the
first article of this sub-series, Black
Belt Administration: Caching Options: Report Session Caching,
we discussed the fact that a common attribute of enterprise reporting systems is
their provision for unified points of maintenance for various aspects of system
configuration. We also noted that Reporting
Services meets the general need for centralized maintenance of reports and
their constituent components by housing them within a central “report catalog,”
facilitating easier report access and management. Through
this means and others, Reporting Services provides multiple management
options to administrators.

In our
introductory comments, we noted that one of many capabilities that Reporting
Services offers administrators is caching. During report
execution, the three basic steps taken by the Report Server include:

• Retrieval of data from the specified data
  source(s);
• Merging of the retrieved data with
  the layout information specified by the report author;
• Generation of the intermediate format
  of the report (which is next turned into the final report output within the rendering
  stage).

Because
the Report Server can cache the intermediate format of the report, we learned, the
time required to retrieve a report can be shortened. This accelerated
retrieval can mean an improved user experience, particularly in cases where the
report is large or accessed frequently. Caching is a performance-enhancement technique that is effective in
many cases, although cache content volatility (the tendency of content
to change as reports are added, replaced, or removed) as well as other factors,
can be a consideration when we choose among the available types of caching.

We stated
that our objective, within the three-part Black Belt Administration:
Caching Options subseries, is to introduce and overview the three types
of caching that Reporting Services 2005 offers
administrators. The three caching types are Report Session, Report
Execution, and Snapshot. In this article, we will explore Report
Execution caching. As a part of our examination of Report Execution
caching, we will:

• Discuss the
  general purpose of Report Execution caching;
• Review details
  about how Report Execution caching operates in Reporting
  Services 2005;
• Explore the
  settings involved, from the perspective of MSSQL Server Management Studio,
  in putting Report Execution caching to work, including system
  defaults for those settings;
• Include other
  information about Report Execution caching that may prove useful
  in selecting or discarding this option for use within our own business
  environments.

Report Execution Caching in Reporting Services 2005

Report Execution caching, one of three caching options within Reporting
Services 2005, is turned off by default. Therefore, the most
basic difference between Report Execution caching and Report Session caching
(the subject of the first article in this sub-series) is that the former can at least be disabled.
(Report Session caching cannot be disabled in the purest sense, as we
noted; Unless a report is configured for “snapshot” treatment, its intermediate
format is automatically cached – at least for the duration of the report
session, a parameter which can, itself, be specified for the Report
Server).

Purpose of Report Execution
Caching, and Details of Its Operation

The primary objective of Report
Execution caching is to provide enhanced reporting performance. This
contrasts with the goal of Report
Session caching, the purpose of which
is to support a consistent viewing
experience during a single browser session (a configurable “report session,”
as we noted in Black Belt Administration: Caching Options: Report Session Caching).

As we noted in the first article of our Caching Options sub-series, most of us are probably aware that
Reporting Services stores temporary files within an underlying database
to support user sessions and report processing. Each Report Server
database uses a related temporary database to store session and execution
data, cached reports, and work tables that are generated
by the Report Server. Caching in general occurs for internal use,
and to support a consistent viewing experience during a single browser session.
Making this happen is the primary objective of Report Session caching.

As we have learned, anytime a report is requested from a
given consumer-client, Reporting Services caches the intermediate
format for the report – not the ultimate report output – within the ReportServerTemp
database. Caching the intermediate format means that varied rendering
options can still be applied upon the cached data (the intermediate
format) to offer the performance benefits of caching (primarily
speed and consistency, as we have noted) while still offering flexibility in
the appearance of multiple renderings.

The operation of Report Execution caching differs
significantly from the operation of Report Session caching. As we
learned in Black Belt Administration:
Caching Options: Report Session
Caching, each consumer-client’s request for a report entails the
creation of a separate report session cached, client-report “version” of
an intermediate format file. Although Report Execution caching is
similar in its employment of the intermediate format that is cached
within the ReportServerTemp database, it has little else in common with Report
Session caching. With Report Execution caching, a single intermediate
format file “version” (at least for each report with the same, or no,
parameters) is shared, once the first client requests the report, among it
and other clients that later request the same report. This
characteristic of the operation of Report Execution caching is depicted
in Illustration 1.

We learned that the primary objective upon which Report
Session caching is based is the support of a consistent viewing experience
during a single browser session – a configurable “report session,” as we
noted. (Performance enhancement is, as we also discovered, a benefit we
obtain. This is a natural result of the use of a cached copy of the
report’s intermediate format by the consumer-client; the retrieved
dataset is already stored and “waiting” for the consumer – it does not have to
be retrieved upon request.) By contrast, the primary objective of Report
Execution caching is enhanced performance.

Administrators
can specify settings for cache expiration. Expiration options
include the capability to set a specific life for the cache (for
example, we can direct that its life not exceed twelve hours), as well as the
capability to specify a fixed schedule for cache expiration (for
instance, we can direct that the cache is expired at midnight and at noon of each day). We thus have the
flexibility to perform cache flushes based upon scheduled OLAP and OLTP
updates, among other data-changing events, so as to provide the most current
information to report consumers in a performance enhanced manner. We can then
even put in place processes to automatically run critical, popular reports “the
first time” after each cache flush to re-instantiate the cache
with the report’s updated
intermediate
format.

To drill down upon the
general operation in a little more detail, we will note that the cached intermediate
format is removed / swapped from
the Execution cache under several conditions. The report is swapped, of
course, when the cached
report expires (expiration
is dependent upon the expiration
settings we have made). Moreover, restart of the Report Server
application domain will also trigger a swap of the cached report. Further, the RS Web Service
Flush-Cache method can be used to explicitly flush an existing intermediate format cache. Finally, changes to the execution settings, report definition file (.rdl), and other
modifications, as well as other events, can result in the Report Server swapping out the cached intermediate format.

To use Report Execution caching,
we have to ensure that a couple of credentials considerations are acceptable
within the environment, and that we comply with these conditions. First, we
must ensure that reports for which we wish to enable Report Execution
caching do not prompt consumers for database login credentials at
runtime. Second, candidate reports cannot employ Windows authentication
– neither for establishing the underlying database connection (“Integrated
Security”), nor within expressions contained in the reports.

We will get some hands-on exposure to
enabling Report Execution caching in the section that follows.

Settings to
Configure Report Execution Caching from MSSQL Server Management Studio

As we have learned, the
primary objective of Report Execution caching is to enhance report
performance by caching temporarily, upon the initial consumer-client
request, reports for which we enable such caching. We make the settings
to enable this capability, as well as specify its operational behavior, within
the report execution properties. Report execution properties control how a report is
processed. Execution properties must be set for each report individually,
although they can be set to reference pre-established defaults, as we shall
see.

We can set Report
execution properties within the SQL Server Management Studio as well
as within the Report Manager interface. In the subsections that follow,
we will examine these settings for a sample report from the perspective of the SQL
Server Management Studio. (We will examine configuration of Report
Execution caching from the Report Manager in Part II of this
article).

If you have not installed
the samples, you’ll need to install them to use the sample report presented in
the steps below. If you prefer to use a local report, the steps will be
approximately the same for accessing the property settings.