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 28, 2007

Report Execution Caching II: Report Manager 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 previous articles of this sub-series, Black Belt Administration: Caching Options: Report Session Caching and Report Execution Caching I, we discussed the fact that a common attribute of enterprise reporting systems is their provision for unified points of maintenance of 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 within both articles, 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 sub-series, 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 continue the exploration of Report Execution caching that we began in Report Execution Caching I, where our hands-on exercises centered upon an MSSQL Server Management Studio perspective. As a part of our continuing examination of Report Execution caching, we will:

  • Review 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 Report Manager, 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, as we learned in Report Execution Caching I. Therefore, the most basic difference between Report Execution caching and Report Session caching (the subject 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).

A Review of the Purpose of Report Execution Caching, and Details of Its Operation

The primary objective of Report Execution caching, as we noted in Report Execution Caching I, 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 previous articles 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 into 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, just as we did in the first half of this article, Report Execution Caching I. Instead of focusing on the configuration of Report Execution caching from within SQL Server Management Studio, however, we will turn our attention to an alternate approach. This time, we will gain some practice with Report Execution caching settings within the Report Manager interface.

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