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.
As we
have noted in other articles of this series, a common attribute of enterprise
reporting systems is their provision for single points of maintenance for various
aspects of system configuration. 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. Reporting Services provides multiple management
options to administrators.
One of
many capabilities that Reporting Services offers administrators is caching.
During report execution, the three basic steps taken by the Report
Server include:
The Report
Server can cache the intermediate format of the report, to shorten the time required to retrieve a
report. 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.
Within
the three-part Black Belt Administration: Caching Options
subseries, we will 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 Session caching. As a part of
our examination of Report Session caching, we will:
The three caching options
within Reporting Services 2005 have numerous characteristics
in common. One way that Report Session caching differs, however, lies
in the fact that it cannot be “disabled” in the purest sense. 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, as we shall see.
Purpose of Report Session Caching
We can begin discussing Report
Session caching with a definition of a report session. Each report
session is associated with a single, requesting consumer client for
a given report. The report session setting represents a modifiable
duration of time within which our Report Server is allowed to serve
report requests 1) from the same consumer client, and 2) from the same intermediate
format that has been cached on the server. The purpose of report
session caching is to support a consistent viewing experience during a
single browser session (a configurable “report session,” as we have
noted).
Because the cached copy of
the report (its intermediate format) is used by the consumer until the report session
expires, we obtain both enhanced performance (the retrieved dataset is
already stored and “waiting” for the consumer – it does not have to be
retrieved upon request) and consistent data. Consistency is
important, in most cases, in that the report data remains static during the
consumer report session – that is, if the source data underlying the
report changes during a report session, the data remains fixed within
the displayed report, or even when the same data is exported via the Report
Server within the same report session.
Details of Report Session Caching Operation
Most of us are 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, as we have noted to be so in the case of Report Session
caching.
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.
As we see in the Illustration 1 below, each client
request entails the creation of a report session cache client-report
“version” of an intermediate format file. If two consumer clients request
the identical report, as is the case with Client A and Client B,
each of whom has requested Report 1, a
separate intermediate format is created and assigned a session
identifier. (Clients C and D in the illustration have requested
different reports.) For each client, for any requests that follow (within the
specified report session duration) with the same client / session
identifier combination, the intermediate format is cached within
the temporary database.
Illustration
1: Report Session Caching: Individual Client Intermediate Formats
To summarize the operation of Report Session caching,
when a client makes a report request, the retrieved data and
report layout are merged into intermediate format, and stored in
the session cache. The intermediate format within the cache can
be rendered multiple times, in multiple formats, within the duration of the report
session. The report session is, itself, configurable
(we will discuss the settings involved in the next section), but we cannot
completely disable Report Session caching.
Settings to Configure Report Session Caching
While we cannot completely disable Report Session caching, we do have
options to adjust settings to adapt its operation to more closely meet our
needs. We can directly control two primary aspects of Report Session caching:
1) we can specify how a client requesting a report is
associated with a report session, and 2) we can set the expiration
interval of the report session (and thus the duration of the session).
Let’s take a look at how we can adjust these settings
within our own environments. To do this, we will work from within the Microsoft SQL Server
Management Studio.
1.
Start Microsoft
SQL Server Management Studio.
2.
Select Database
Engine in the Server type selector of the Connect to Server
dialog that appears.
3.
Type / select
the server name (server name / instance, if appropriate) in the Server name
selector.
4.
Supply
authentication information, as required in your own environment.
The Connect to Server
dialog appears similar to that depicted in Illustration 2.
Illustration 2:
Connecting to the Server …
5.
Click the Connect
button to connect with the specified server.
The Microsoft SQL
Server Management Studio opens.
6.
In the Object
Explorer pane (it appears by default on the left side of the Studio),
expand the Databases folder (click the “+” sign to its immediate left),
appearing underneath the server within which we are working.
7.
Expand the ReportServer
database (click the “+” sign to the immediate left of its icon).
8.
Expand the Tables
folder to expose the database tables.
9.
Right-click
the ConfigurationInfo table.
10.
Select Open
Table from the context menu that appears, as shown in Illustration 3.
Illustration
3: Opening the ConfigurationInfo Table
The ConfigurationInfo table
opens. Here we can make modifications to adjust the operation of Report
Session caching. There are two controllable options for this type of caching.
First, we can adjust the UseSessionCookies setting, which allows us to
dictate how a requesting information consumer client is associated with
a report session. Default setting for this property is “true,”
although we have the option for setting this to “false,” and disallowing
the use of session cookies. The “false” alternative will direct the
Report Server to embed the session identification information into the URL
address for the report involved.
The other configuration
option that is available to us with Report Session caching lies
in our ability to set the duration of the report session by
adjusting the expiration interval. We manage expiration interval
via the SessionTimeout setting. Default for the setting is ten (10) minutes
(or 600 seconds).
The default UseSessionCookies
and SessionTimeout settings appear as depicted in Illustration 4.
Illustration 4: ConfigurationInfo
Table – Report Server Caching Settings
When making changes to the UseSessionCookies and SessionTimeout
settings, we will, of course, want to consider such things as their impact
upon overall security and any risks involved in presenting stale data,
respectively. And while there are a couple of ways, from a technical
perspective, of “refreshing” the data at any time by clearing the current report
session, consumers can also accomplish this by using the Refresh Report
button within the report toolbar, as appropriate.
In this
article, we introduced a three-part subseries surrounding caching
options in Reporting Services 2005. We began by overviewing
the three types of caching that Reporting Services 2005 offers:
Report Session, Report Execution, and Snapshot. We then
introduced the first of these three, and launched our overview of Report
Session caching.
As a part
of our examination of Report Session caching, we discussed the general
purpose of this first caching type. We next reviewed details about how Report
Session caching is accomplished in Reporting Services 2005.
Finally we explored the settings involved in putting Report Session caching
to work, including system defaults for those settings. Throughout the various
sections of the article, we discussed other information about Report Session
caching in an attempt to assist in selecting or discarding this option
for use within our own business environments.
»
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.
Subscribe to Cloud Insider for top news, trends & analysis
DatabaseJournal.com publishes relevant, up-to-date and pragmatic articles on the use of database hardware and management tools and serves as a forum for professional knowledge about proprietary, open source and cloud-based databases–foundational technology for all IT systems. We publish insightful articles about new products, best practices and trends; readers help each other out on various database questions and problems. Database management systems (DBMS) and database security processes are also key areas of focus at DatabaseJournal.com.
Advertise with TechnologyAdvice on Database Journal and our other IT-focused platforms.
Property of TechnologyAdvice.
© 2022 TechnologyAdvice. All Rights Reserved
Advertiser Disclosure: Some of the products that appear on this site are from companies from which TechnologyAdvice receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. TechnologyAdvice does not include all companies or all types of products available in the marketplace.
Leave a Reply