Report Execution Caching I: SQL Server Management Studio Perspective

Monday Sep 17th 2007 by William Pearson
Share:

BI Architect Bill Pearson continues a three-part sub-series on Caching Options within Reporting Services 2005. In this article, we focus upon Report Execution Caching, and its configuration from SQL Server Management Studio.

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.

Configure Report Execution Caching from the SQL Server Management Studio

Let’s take a look how we can adjust these settings for a report within the SQL Server Management Studio. To do this, we will take the following steps:

1.  Start Microsoft SQL Server Management Studio.

2.  From within the SQL Server Management Studio, click the Connect button in the Object Explorer pane (by default, it appears in the lower left corner of the Studio environment).

3.  Select Reporting Services... in the dropdown menu that appears, as shown in Illustration 2.


Illustration 2: Connecting to Reporting Services ...

4.  Select Reporting Services in the Server type selector of the Connect to Server dialog that appears, if necessary.

5.  Type / select the server name (server name / instance, if appropriate) in the Server name selector.

6.  Supply authentication information, as required in your own environment.

The Connect to Server dialog appears similar to that depicted in Illustration 3.


Illustration 3: Connecting to the Server ...

7.  Click the Connect button to connect with the specified server.

The Microsoft SQL Server Management Studio opens. The Reporting Server to which we have connected appears atop the Object Explorer pane.

8.  In the Object Explorer pane, expand the Home folder (click the “+” sign to its immediate left), appearing underneath the server within which we are working.

9.  Navigate to, and expand, the subfolder containing a sample report with which to complete the steps of this section, as appropriate (my sample reports have been deployed within the AdventureWorks Sample Reports folder, and appear, with this step, as shown in Illustration 4.


Illustration 4: The Sample Reports Appear

10.  Right-click the Sales Reason Comparisons report (or another report of your choice).

11.  Select Properties from the context menu that appears next, as depicted in Illustration 5.


Illustration 5: Accessing Report Properties from the Object Explorer ...

The Report Properties dialog for the selected report opens.

12.  Click Execution in the Select a page pane on the left side of the dialog.

13.  On the Execution page that appears to the right, click-select the radio button to the immediate left of Render this report with the most recent data, if necessary.

Our intent is to configure the report to run “on demand,” from the cache, as we shall see with our next settings, where we schedule the expiration of the cached report, using each of two possible options.

To make the cached copy expire after a particular time period:

14.  Click Cache the report. Expire after (minutes).

15.  Type the number “30” in the selector box to the immediate right.

Our settings on the Execution page for a half-hour expiration of the cached report appear as shown in Illustration 6.


Illustration 6: Settings for Expiration of Cached Report after a Specified Time Period

To make the cached copy expire on a schedule:

16.  Click Cache the report. Expire based on the following schedule.

17.  Click Configure.

The Create Custom Schedule page appears.

18.  Type / select today’s date in the selector box that appears to the right of the label Begin running this schedule on.

19.  Under Recurrence Pattern, ensure that Day is selected in the box labeled Type.

20.  Leave the Start Time, in the setting just underneath, at 2:00:00 AM.

21.  Click-select the radio button to the immediate left of Every weekday.

Create Custom Schedule page appears, with our settings, as depicted in Illustration 7.


Illustration 7: Create Custom Schedule Page with Our Settings

Our settings above dictate a custom schedule for expiration of the cache of this specific report. Using such a fixed schedule is ideal in many scenarios, where we want to mandate Report Server processing at a specific time. An example might be setting the execution cache to expire at 2:00 AM every Monday morning, in a situation where, say, we update sales data and refresh our cubes by midnight each Sunday for reporting the following week. This means, of course, that the newly updated sales data is reflected when information consumers begin requesting reports after the opening of business on Monday morning.

22.  Click OK to accept our settings input, and to dismiss the Create Custom Schedule page.

We are returned to the Execution page, where a summary of our new settings appear as shown in Illustration 8.


Illustration 8: Execution Page Displaying Our Settings for Scheduled Cache Expiration ...

Finally, an additional setting, Report Execution Timeout Defaults, lies at the bottom of the Execution page. Here we can set a limit on the amount of time allowed for report processing – one of several “governor” properties we can set surrounding our reports to prevent “runaway conditions,” as well as perhaps to minimize the risk of overtaxing system resources in other ways. Our options for timeout default settings include:

  • Use the default setting - directs the Report Server to use the default report execution timeout value as it is globally defined for the Report Server (which we specify within the Report Server properties, on the Server Properties dialog, Execution page, as depicted in Illustration 9).


Illustration 9: Execution Page of the Server Properties Dialog – Global Timeout Default

  • Do not timeout report execution - directs the Report Server to impose no limitations on report execution time.
  • Limit report execution to the following number of seconds - timeout value is defined as a specific number of seconds (with the defaulted selector value the same as the global default setting that is defined in the corresponding Server Properties setting, as noted in the Use the default setting option above).

23.  Click OK to close the Execution page, and to change all settings back to their original positions.

24.  Exit SQL Server Management Studio when ready.

Conclusion

In this article, we continued a three-part subseries surrounding caching options in Reporting Services 2005. We began by briefly reviewing the three types of caching that Reporting Services 2005 offers. We then introduced the second of these three, and launched our overview of Report Execution caching.

As a part of our examination of Report Execution caching, we discussed the general purpose of this second caching type. We next reviewed details about how Report Execution caching is accomplished in Reporting Services 2005. Finally we explored the settings involved in putting Report Session caching to work, within the MSSQL Server Management Studio environment (with an eye toward addressing the same settings from a Report Manager perspective in the second half of this article), including system defaults for those settings. Throughout the various sections of the article, we discussed other information about Report Execution 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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved