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

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.