MSSQL Server Reporting Services: Black Belt Administration: "Governor" Capabilities: Report Execution Timeout

Monday Mar 21st 2005 by William Pearson
Share:

Control demands upon critical enterprise resources by Reporting Services with Report Execution Timeouts.MSAS Architect Bill Pearson introduces one of several means for "governing" the system demands of information consumers, developers and other Reporting Services users.

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"), with the objective of presenting an overview of its features, together with many tips and techniques for real-world use. For more information on the series, as well as the hardware / software requirements to prepare for the exercises we will undertake, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting.

Basic assumptions underlying the series are that you have correctly installed Reporting Services, including Service Pack 1, along with the applications upon which it relies, and that you have access and the other rights / privileges required to complete the steps we undertake in my articles. For details on the specifics of the adjustments necessary to quickly allow full freedom to complete the exercises in this and subsequent articles, as well as important assumptions regarding rights and privileges in general, please see earlier articles in the series, as well as the Reporting Services Books Online.

This article also relies upon sample files that may not have been automatically installed, configured, and / or deployed within your Reporting Services installation. If the samples have not been installed in, or were removed from, your environment, the samples can be found on the Reporting Services installation CD. We will discuss accessing these files within the steps of our practice session.

About the BlackBelt Articles ...

As we have stated in earlier BlackBelt articles, one of the greatest challenges in writing tutorial / procedural articles is creating each article to be a freestanding document that is complete unto itself. This is important, because it means that readers can complete the lesson without reference to previous articles or access to objects created elsewhere. When our objective is the coverage of a specific technique surrounding one or more components of a report, a given administrative function surrounding all reports, and other scenarios where the focus of the session is not the creation of reports, per se, achieving our goals can be challenging because a report or reports often has to be in place before we can begin to cover the material with which the article concerns itself.

The BlackBelt articles represent an attempt to minimize the setup required in simply getting to a point within an article where we can actually perform hands-on practice with the component(s) under consideration. We will attempt to use existing report samples or other "prefabricated" objects that either come along as part of the installation of the applications involved, or that are readily accessible to virtually any organization that has installed the application. While we will often have to make modifications to the sample(s) involved (we will typically create a copy, to allow the original sample to remain intact), to refine it to provide the backdrop we need to proceed with the object or procedure upon which we wish to concentrate, we will still save a great deal of time and distraction in getting to our objective. In some cases, we will have to start from scratch with preparation, but my intention with the BlackBelt articles will be to avoid this, if at all possible.

For more information about the BlackBelt articles, see the section entitled "About the BlackBelt Articles" in BlackBelt Components: Manage Nulls in OLAP Reports.

Overview

In working with many reporting tools over my career, particularly within enterprise business intelligence suites and high-end production reporting systems, I have become acquainted with various control features in each that allow administrators to govern the actions of end users. Reporting Services is no different in this aspect of need for control, and the application provides numerous avenues for restraining our users from kicking off resource crippling queries that, unchecked, could disrupt even the most robust systems, as well as to prevent other harmful activities. At various junctures within this series, we will examine ways to effect such controls on an intermittent basis.

A typical example of such control might be the need to limit report processing time. In Reporting Services, we are provided a means of accomplishing this with the Report Execution Timeout setting. In addition to the setting itself, which is straightforward enough, we need to consider another, more global setting as well, so as to ensure that the two interact properly to produce the results we desire. Our focus in this article will be an examination of these settings and how they work together to afford us a means of providing an important control over resource utilization within Reporting Services.

In this session, we will:

  • Discuss how the Report Execution Timeout setting provides us one means of control over report processing demands;
  • Prepare for our practice session by creating a clone of one of the sample reports that accompany Reporting Services, along with the respective data source file;
  • Publish the clone report for use in our practice from the Report Manager;
  • Examine and discuss the default settings for the Report Execution Timeout;
  • Discuss important considerations in the use of the Report Execution Timeout from the perspective of the "sweep cycle" default within Reporting Services, which evaluates the Report Execution Timeout for enforcement of our settings;
  • Perform an exercise whereby we gain practice in setting both parameters for effective use of the Report Execution Timeout.

Understanding Report Execution Timeout

Objective and Business Scenario

At the heart of the Report Processing cycle in Reporting Services, lies the Intermediate Format of the report. Once the report definition (in the .rdl file) and the data are married, the resulting Intermediate Format is stored in the Report Server database. The resource-intensive process of querying the data source, and performing the activities required to generate the report layout (as defined in the .rdl file by the report author) is thus accomplished in a single cycle. Rendering of the report then becomes a separate, subsequent step whereby various rendering extensions can be called upon to deliver the same Intermediate Format, which is stored as a Binary Large Object (BLOB) inside the Report Server database. We will look more closely at ways to leverage storage of the Intermediate Format in subsequent articles, where we examine caching, Snapshots and Historical Reports as further management opportunities within Reporting Services.

The focus of this article is the restraint of runaway demands at the level of Intermediate Format generation. To regulate, or govern, our users with regard to limiting such queries, we can leverage the Report Execution Timeout setting for the entire report collection under our administration, or at the level of individual reports. What we need to understand is not only where and how to change the default Report Execution Timeout setting (at the interface level), but another setting that must be taken into consideration if we are to obtain precisely the effects we intend with the Report Execution Timeout setting itself. I refer to this as the "evaluation sweep" for the Report Execution Timeout setting, and we will examine it closer in our practice exercise.

Reasons for restricting query execution time are legion. Among them, it can be particularly useful to put this control in place when supporting authors who graphically write the queries that serve as the basis for reports, who may not understand or fully appreciate optimization techniques, and so forth. This becomes especially significant when novice query writers venture beyond simple queries, and where multiple joins and extensive selection criteria become involved. Unwanted crossjoins and table scans are only a few of the results that can overburden the RDBMS, as well as create a large increase in network traffic. In addition, indexing may not have been taken into consideration, and the author may not even be aware of its importance. Moreover, even if the query runs fine in a smaller development environment, an unregulated report may choke upon its debut into a larger / more complex production environment, and cause a disruption in mission critical evolutions.

The Report Execution Timeout setting can be used as a safeguard to prevent many such issues, indeed, and serves as one of many tools for restricting authors / consumers from performing activities that might be excessively time consuming or otherwise less than beneficial.

Let's set the stage for our practice session with a hypothetical example, upon which we can build a scenario to get some hands on exposure to Report Execution Timeout settings. For purposes of this article, let's say that we have begun coordination with the database administrator for a client within which we are implementing Reporting Services. The DBA tells us that he prefers restrictions upon the length of time developmental authors' queries can run, as much activity is concentrated within the server upon which our development database and other applications are located. While resources allocated to the reporting effort will expand in production, we are faced with the need to share a developmental server with unrelated projects at present, like it or not. We want to restrict lengthy queries and the burden that the DBA fears they will place upon the server, as well as upon the corporate network, which, unsurprisingly, connects various developers who compete for the server's processing time, together with the general corporate population.

As this often happens, we suppress the response that we were promised a dedicated server. After all, we virtually always hear such promises in the planning stages, but usually find out, after the fact (and sometimes even accidentally), that other development efforts are being allocated to our server in the ongoing budget shell games that plague business today. (I am no longer surprised to see tens of thousand of dollars in consulting fees wasted while consultants - even offshore developers - await access to resources, when a sizable server could have been purchased and brought online for only a tiny fraction of the cost. But we won't go there ...). We had planned to place controls on author activities anyway, so we simply indicate to the DBA that we have a list of actions that we will take. One of these, we specifically note, will be to limit query execution time.

The DBA, who is new to Reporting Services, is happy to hear that we can establish control over query execution time; this was a capability that existed in the Business Objects environment, with which the DBA is more familiar, that existed within the enterprise prior to our beginning the current conversion to Reporting Services. He states that he wishes to set a global timeout limitation of fifteen (15) minutes, to apply to our entire population of reports for the time being, but also asks that we establish a different timeout setting for a report designated as "mission critical," which is known to run a bit longer than others do. This report, once it is moved into production, will be scheduled at night in a timeframe where it will have time to run without the risk of resource disruption.

Considerations and Comments

In working with the Report Execution Timeout setting, we will need access to a sample report that is installed and published in Reporting Services. For purposes of this exercise, as in most of the exercises we perform in articles of this and other series, we will prepare a copy of the Product Line Sales sample report that accompanies the installation of Reporting Services, along with other samples. The "clone," which will serve as the "exception" report whose Report Execution Timeout setting will be different from that of the general report population, will allow us to leave the original sample report in its pristine (or otherwise existing) condition, as we might have saved various settings, structures, and so forth, for referential or other reasons. There will therefore be no need to remember to return and restore the original sample to its previous state. We can simply discard our clone upon the conclusion of our session, or at any convenient time thereafter.

While the cloning process is simple, ensure that you have the authority, access and privileges needed to accomplish the process, as well as a place to store the copy of the sample report outside of its original location. After the session, the clone can be deleted or used for another purpose - again, whatever is convenient.

If the sample reports were not installed, or if the Product Line Sales report was removed prior to your beginning this article, please see the Reporting Services documentation, including the Books Online, for straightforward instructions for obtaining the sample files. As of this writing, a copy of the samples set can be obtained from the installation CD or via download from the appropriate Microsoft site(s).

To complete the procedures we will undertake in this article, you will need to have the appropriate access and privileges, at the MSSQL Server 2000 level, and within Reporting Services, MSSQL Server Analysis Manager, and the file system, to perform the respective actions. You will also need access to the Reporting Services installation CD, from which we will be copying the sample report files to our local drive.

Hands-On Procedure

Preparation

Create a Clone Report and Data Source for the Practice Exercise

Let's first copy an existing report to provide a disposable work environment, for part of our practice session. When we installed Reporting Services, the sample report files were copied to the PC, with the default installation point being the Samples folder within the Reporting Services program folder. A common example of this default path is as follows:

C:\Program Files\Microsoft SQL Server\
MSSQL\Reporting Services\Samples\Reports

1.  Enter the folder that contains the sample reports.

2.  Click the AdventureWorks.rds data source file to select it.

3.  Hold the CTRL key while clicking the Product Line Sales.rdl file.

The intent here is to select both files simultaneously.

4.  Right-click, with the two files selected.

5.  Select Copy from the context menu that appears, as shown in Illustration 1.


Illustration 1: Select Copy from the Context Menu

6.  Navigate to a convenient location to place the copy.

7.  Right-click within the folder chosen.

8.  Select Paste from the context menu that appears, as depicted in Illustration 2.


Illustration 2: Select Paste from the Context Menu

The Product Line Sales.rdl and the AdventureWorks.rds files appear in the new location.

9.  Right-click Product Line Sales.rdl in the new location.

10.  Select Rename from the context menu that appears, as shown in Illustration 3.


Illustration 3: Select Rename from the Context Menu

11.  Rename the file as follows:

RS015.rdl

We now have a disposable report that we can use in Report Designer without fear of damaging a potentially useful sample, as well as a report data source. Next, we will create a project in Visual Studio, from which to work, and then return to the report as a step in our practice session with the Report Execution Timeout settings.

Create the Report Project

To begin, we will launch Reporting Services' Report Designer, found in Microsoft Visual Studio .NET 2003.

1.  Click Start.

2.  Navigate to the Microsoft Visual Studio .NET 2003 in the Programs group, as appropriate.

The equivalent on my PC appears as depicted in Illustration 4.

Click for larger image

Illustration 4: Beginning in Microsoft Visual Studio .NET 2003 ...

3.  Select File --> New from the main menu.

4.  Click Project from the cascading menu, as shown in Illustration 5.

Click for larger image

Illustration 5: Selecting a New Project

The New Project dialog appears. We note that Business Intelligence Projects appears in the Project Types tree, indicating an installation of Reporting Services (the folder was added by the installation of Reporting Services, as it established the Report Designer in Visual Studio .NET).

5.  Click Business Intelligence Projects in the Project Types tree, if necessary.

6.  Click Report Project in the Templates list.

7.  Navigate to a location in which to place the Report Project files. (I suggest using the same location as that containing the sample copies we created earlier.)

8.  Type the following into the Name box, leaving other settings at default:

RS015

The New Project dialog appears, with our addition, as depicted in Illustration 6.


Illustration 6: The New Projects Dialog, with Addition

9.  Click OK.

Our new project appears in the Solution Explorer (upper right corner of the Visual Studio .NET interface), as we see in Illustration 7.


Illustration 7: The New Project Appears in the Solution Explorer

We have now created a Report Project, and are ready to proceed with establishing connectivity with a data source, and then opening the new clone Report File.

Add the Report Data Source

We learned in Managing Reporting Services: Data Connections and Uploads that when we refer to a data source in Reporting Services, we are referring to a collection of properties, in effect, that represents a connection to a given data source. This collection of properties has a name, as it does in other applications where data sources exist (a scenario with which most of us are familiar). Data sources contain the following, where applicable:

  • Specification of the data processing extension we use to process queries of the type for which we intend to use the connection;
  • A connection string that allows us to locate the source;
  • Access credentials involved in allowing us to read the data within the source.

As we have noted in various articles of the series, a data source connection can be embedded in a report (where it is typically defined within the creation process); it can also be defined as a shared data source item that is managed by a Report Server. The data source connection can be created either within the Report Designer in Visual Studio .NET, or at within the Report Manager. In this section, we will "import" the data source to which our sample report clone is already pointed, to make things quick and easy. This way, we can concentrate on Report Execution Timeout settings, the primary focus of the article.

1.  From within the Solution Explorer, right-click the Shared Data Sources folder that appears underneath the new RS015 project we created earlier.

2.  Select Add on the context menu that appears.

3.  Select Add Existing Item from the cascading menu that appears next, as shown in Illustration 8.


Illustration 8: Select Add --> Add Existing Item

The Add Existing Item dialog for the project appears.

4.  Navigate to, and select, the AdventureWorks.rds file that we copied to its new location, as depicted in Illustration 9.


Illustration 9: Select the AdventureWorks Data Source File in the Add Existing Item Dialog

5.  Click Open.

AdventureWorks.rds is added to the folder, as shown in Illustration 10. (The Shared Data Sources folder is expanded in order to see the file.)


Illustration 10: The AdventureWorks.rds File Appears within the Shared Data Sources Folder

All that remains in our preparation phase is to add, test and publish the clone report.

Add the Sample Report to the Project

We can add our clone report file, RS015.rdl, to the project, in a series of steps that closely mirror those with which we added the data source above.

1.  From within the Solution Explorer, right-click the Reports folder that appears within the new RS015 project.

2.  Select Add on the context menu that appears.

3.  Select Add Existing Item from the cascading menu that appears next, as shown in Illustration 11.


Illustration 11: Select Add --> Add Existing Item

The Add Existing Item - RS015 dialog appears.

4.  Navigate to the location of the RS015.rdl report file created earlier.

5.  Select the Report File in the dialog.

The Add Existing Item - RS015 dialog, with the targeted file, appears as depicted in Illustration 12.


Illustration 12: Add Existing Item - RS015 Dialog

6.  Click Open (or double-click the selection) to close the dialog.

Report File RS015 appears in the Reports folder of the RS015 project. Let's test it for data source connectivity.

7.  Right-click RS015.rdl in the Reports folder.

8.  Select Open, as shown in Illustration 13, from the context menu that appears.


Illustration 13: Select Open from the Context Menu ...

Report RS015 opens and appears on the Layout tab of the Report Designer, as depicted in Illustration 14.


Illustration 14: RS015 Appears in Report Designer - Layout Tab (Partial View)

9.  Click the Preview tab to execute the report.

RS015 executes briefly, and then returns data, based upon the default parameters provided atop the report. We can thus rely upon the fact that we have established connectivity with the data in the AdventureWorks2000 database. (We will not be working with drillthrough and other functions within this report sample at present. The report is only here as an example for limited use in the final section of our practice session.)

Deploy the Clone Report for Later Use

Let's deploy the report, publishing it to Report Manager along with its data source, where we will work with it after we examine the process of setting our "global" Report Execution Timeout value in the next section. First, we need to assign deployment properties to our project.

1.  From within the Solution Explorer, right-click the RS015 project we created earlier (not the RS015.rdl file, but the project folder itself).

2.  Select Properties from the context menu that appears, as shown in Illustration 15.


Illustration 15: Selecting Properties from the Context Menu ...

The Property Pages dialog opens, defaulted to the General page, for the RS015 project.

3.  Make the settings, listed in Table 1 below, for General Properties (Deployment):

Property

Setting

OverWriteDataSources

True

TargetFolder

RS015

TargetServerURL

http://<Server Name>/ReportServer


Table 1: General - Deployment Properties Dialog

NOTE: For TargetServerURL, substitute the appropriate server name for your own Reporting Services environment.

The General Properties page, Deployment properties, appears, using the settings on my server as an example, as depicted in Illustration 16.


Illustration 16: Properties Dialog Box with Settings

4.  Click OK to accept changes and to exit the RS015 Property Pages.

We return to the Report Designer. Let's publish the clone report.

5.  From within the Solution Explorer, right-click the RS015 project (not the RS015.rdl file, but the project folder itself), as before.

6.  Select Deploy from the context menu that appears, as shown in Illustration 17.


Illustration 17: Selecting Deploy from the Context Menu ...

Deployment begins, and completes successfully, as we can note from the Output window in Report Designer (depicted in Illustration 18).


Illustration 18: Successful Deployment Events, as Displayed in the Output Window

NOTE: Any errors in publishing appear in the Task List window in Report Designer.

We will verify the successful publication of the report in the next section, where we move to Report Manager to examine the Report Execution Timeout setting. We have now established a sample report to revisit, once we have looked at Report Execution Timeout settings at a more global level.

7.  Select File --> Save All from the main menu, to protect our work for a visit later.

8.  Select File --> Exit to close the Report Designer and Visual Studio .NET.

Procedure

Setting the Global Report Execution Timeout

As we have discussed, we can limit the amount of time for processing a report within Report Manager: Report Execution Timeout value can be set "globally" in Report Manager, where it serves as the default Report Execution Timeout for all reports. Even though such a default is set, we can override it, and provide custom Report Execution Timeout values, for individual reports, as we shall see later in our practice session. First, let's take a look at setting the Report Execution Timeout for the entire report population from Report Manager.

We will begin by accessing Report Manager.

1.  Click Start.

2.  Navigate to the Reporting Services program group that installs within a typical setup. The equivalent on my PC appears as shown in Illustration 19.

Click for larger image

Illustration 19: Navigate to Report Manager ...

3.  Click Report Manager to initialize the application.

NOTE: If Report Manager does not appear in the manner shown, whether because you declined setup of the program group, a disablement of the feature, or other, unknown reason, simply get there by typing the appropriate URL into the address bar of your web browser. The default URL is as follows:

 http://<webservername>/reports

As an example, my <webservername> would be MOTHER1, the name of my server, and would appear, in this approach, in my browser Address line as depicted in Illustration 2.


Illustration 20: Navigate to Report Manager ...

We arrive at the Home page of the Report Manager, Folder View, Content tab, where we see the new RS015 folder appear (circled below), as shown in Illustration 21.


Illustration 21: Report Manager Folder View

4.  Click Site Settings, in the upper right corner of the browser (known as the Global Toolbar), as depicted in Illustration 22.


Illustration 22: Click Site Settings ...

We arrive at the Site Settings page. As many of us are aware, this is where we enable various features and set default values for a Report Server Web site. Site Settings can only be accessed by those with report server administrator privileges.

While we will return to the Site Settings page in prospective articles, we will focus at this point on the Report Execution Timeout setting that appears within the associated section, about halfway down the page. The Report Execution Timeout settings are composed of two checkboxes, one with a value box, which appear under the heading Report Execution Timeout.

Reporting Services installs with a default setting of "enabled" limited execution, with a default value of 1800 seconds / 30 minutes. Recall that the DBA for whom we are performing our services would like to impose an execution limit of 15 minutes on the general report population. Let's make a change to the Report Execution Timeout value on the Site Settings page to meet the requirement.

1.  Ensure that the checkbox to the left of Limit report execution to the following number of seconds: is selected.

2.  Replace the value in the box to the right with the following number:

900

The affected line in the Report Execution Timeout section of the Site Settings page appears as shown in Illustration 23.


Illustration 23: Report Execution Timeout Section of Site Settings with Modifications

3.  Click Apply to apply the modifications.

4.  Return to the Home page of Report Manager (click Home to the left of Site Settings, in the Global Toolbar, from whence we jumped to the Site Settings page earlier).

In setting the Report Execution Timeout at 900 seconds, we have enacted a cancellation of report processing for any report for which the preparation of the Intermediate Format (discussed in the Objective and Business Scenario section above) exceeds 15 minutes. Upon cancellation, the individual attempting to execute the report will receive a message that informs him / her of the fact that processing has been cancelled due to timeout.

As we can see, setting the "global" Report Execution Timeout itself is quite simple. There is, however, a further consideration in rendering our setting effective. We will look at that consideration in the next section.

5.  Leave Report Manager open at the Home page. We will return to it in a subsequent section.

Considering the Configuration File "Sweep" Setting

We need to consider a setting in the configuration file for Reporting Services before we can assume that we are really going to obtain an effective Report Execution Timeout. The RSReportServer.config file is yet another collection of settings whose importance in the administration of Reporting Services will become obvious after working with the application for only a brief time. RSReportServer.config houses a host of settings whose associations include such types as:

  • other report execution
    • Rendering
    • data processing
    • event processing
  • subscription
  • delivery
  • services
  • cache management
  • session management
  • web farm / multi-instance configurations
  • session management

Of particular interest from the perspective of this article is the RunningRequestsDbCycle setting within the RSReportServer.config file. This is what I like to call a "sweep" setting, because it specifies how often an activity or characteristic is "evaluated." Specifically, the RunningRequestsDbCycle setting dictates how frequently the Reporting Services Report Server evaluates currently running jobs, to ascertain whether these activities have exceeded the respective Report Execution Timeout setting in effect. (The "degree of freshness" of executing job information in the Manage Jobs page is also dependent upon the RunningRequestsDbCycle setting.)

The RSReportServer.config file is located in the ReportServer folder of the Reporting Services installation folder. A common location on many servers might be the following:

C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer

To reinforce our understanding of the setting, let's open the RSReportServer.config file, and examine the setting we're discussing:

1.  Right-click Start.

2.  Select Explore to open Windows Explorer.

3.  Navigate to the RSReportServer.config file (mine is located in the path shown above).

4.  Right-click the RSReportServer.config file.

5.  Select Open With from the context menu that appears.

6.  Select Notepad from the cascading menu that appears next, as depicted in Illustration 25.


Illustration 25: Select Open With -> Notepad

RSReportServer.config opens in Notepad.

7.  Select Edit -> Find from the Notepad main menu.

The Find utility appears.

8.  Type the following into the Find What box of the dialog:

RunningRequestsDbCycle

The completed Find dialog appears as shown in Illustration 26.


Illustration 26: Find Dialog with Our Input

9.  Click Find Next.

The Find utility quickly locates the RunningRequestsDbCycle setting.

10.  Click Cancel to close the Find utility.

The RunningRequestsDbCycle setting appears as depicted in Illustration 27.


Illustration 27: RunningRequestsDbCycle Setting in RSReportServer.config

The default, which appears in Illustration 27 above, is 60 seconds. While we will not make changes to the RSReportServer.config file in this practice session, it is important that we consider how the RunningRequestsDbCycle value interacts with the Report Execution Timeout setting, and how its injudicious use might render the Report Execution Timeout setting ineffective. The setting of this "evaluation sweep" determines how frequently Report Server performs a comparison between the accumulated processing time of the report and the Report Execution Timeout value we established in Site Settings above. If the value of the "sweep" cycle (RunningRequestsDbCycle) - the "comparison event" - is large enough to contain actual report run time within it, then report processing will complete regardless of the Report Execution Timeout.

As an example, let's say we set the Report Execution Timeout to 20 seconds, and we modify the RunningRequestsDbCycle setting in the RSReportServer.config file to 45 seconds. Let's further say that the report actually takes 40 seconds to process. It becomes easy to see how, although the report has clearly exceeded the Report Execution Timeout limitation, the event will not be detected if the processing time range occurs "between sweeps," (which only happen at 45-second intervals. Thus, the difference between Report Execution Timeout and actual runtime of 20 seconds (actual run time of 40 seconds less Report Execution Timeout of 20 seconds) is not captured within the interval between sweeps. The overall effect, therefore, is that the Report Execution Timeout is completely ignored, because the triggering difference "flies below radar."

Having met the DBA's expressed requirement for a global timeout limitation of 15 minutes for the general report population, and having discussed the important (albeit not significant in our present example) consideration of the interaction between the Report Execution Timeout setting and the RunningRequestsDbCycle "sweep" interval, we are ready to examine the fulfillment of the DBA's request that we enable an exception to the global Report Execution Timeout. We will apply an exception to our clone report in the next section.

11.  In Notepad, select File -> Exit, closing RSReportServer.config without making changes.

Setting Report Execution Timeout for an Individual Report

As a part of our business requirements gathering process in the Objective and Business Scenario section earlier, we learned that one of the reports administered by the DBA would need to have an exception timeout value applied. The Product Line Sales report, which we cloned and renamed RS015 to safeguard the original for the time being, has been described as "mission critical," and is expected to run somewhat longer than other reports. We ask the DBA for an acceptable timeout setting for the report, and he replies that 35 minutes would be a good value.

Let's impose this exception from the default Report Execution Timeout at the level of the report in question, by returning to Report Manager and taking the following steps.

1.  Return to Report Manager - Home page.

2.  Click the RS015 folder, created when we published our clone of the Product Line Sales as RS015, as shown in Illustration 28.

Click for larger image

Illustration 28: Click the RS015 Report Folder

The report and its data source connection appear, as depicted in Illustration 29.


Illustration 29: RS015 and its Data Source Connection Appears

3.  Click the link for report RS015 to open it.

The report begins to execute, based upon the default parameter entries that appear.

4.  Click the Properties tab, atop the report window, as shown in Illustration 30.


Illustration 30: Click the Properties Tab

5.  Click Execution, a link in the upper left of the Properties page, as depicted in Illustration 31.


Illustration 31: Click Execution ...

We arrive at the Execution page for report RS015. Here we will override the "global" Report Execution Timeout, and specify the timeout value for this specific report.

6.  Under the bottom section of the page, titled Report Execution Timeout, click the radio button to the immediate left of Limit report execution to the following number of seconds.

7.  Type the following numeric value into the box that follows Limit report execution to the following number of seconds:

2100

The Report Execution Timeout section, with our modifications, appears as shown in Illustration 32.


Illustration 32: Report Execution Timeout Settings Section

8.  Click Apply to accept and apply our settings.

We have thus set the clone of the Product Line Sales report to time out at 35 minutes, in accordance with the requirement expressed by the DBA. In like manner, the timeout value for any report can be individually modified as an exception to the "global" Report Execution Timeout value we enforce within Site Settings for Reporting Services.

9.  Click Home in the Global Toolbar to return to the Home page of Report Manager.

10.  Close the browser, closing Report Manager, when ready.

NOTE: Be sure to remember to restore all timeout values set in the article as appropriate for your own Reporting Services environment.

Summary and Conclusion ...

In this article, we discussed the use of the Report Execution Timeout parameter as a means of control over report processing demands within Reporting Services. After preparing for our practice exercises by creating a "clone" report, and then creating a Visual Studio .NET project to house it, we published the report with a working data source connection to use within the session. We then focused upon the default settings for the Report Execution Timeout, changing the setting as the first steps of our practice exercise.

After getting some hands-on exposure to setting the global Report Execution Timeout, we examined the "sweep" setting in the Reporting Services Configuration file, and discussed how it interacts with the Report Execution Timeout setting, to bring attention to the fact that the injudicious use of the setting can render our Report Execution Timeout ineffective. Finally, we extended our practice session to include setting Report Execution Timeout at an individual report level, as a means of demonstrating how we might obtain exceptions to the global Report Execution Timeout setting we made of Reporting Services at the Site Settings level.

» 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