Understanding Report Execution Timeout
Objective and Business Scenario
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.
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.
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.
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.
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.
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
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.