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.
» 