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.



Illustration
1: Report Execution Caching: Clients Share a Single Cached Intermediate Format

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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles