Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 17, 2007

Report Execution Caching I: SQL Server Management Studio Perspective

By William Pearson

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.


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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM