Optimizing Microsoft SQL Server 2000 Reporting Services: Performance and Access Reports from the Execution Log

The sample files also include the Visual Studio project (.rptproj) and solution (.sln) files for the report set, together with a data source connection file (.rds).The reports and objects are designed to be used with the Execution Log database we created and populated in our last session. A database diagram of the database is shown in Figure 1.
 

Figure 1: Simple Database Diagram (MS Visio) of the New Reporting Database

We discussed reasons for creating a reporting database as opposed to simply using the Execution Log in its original state in our previous session. We then opened and executed the provided table creation script, using MSSQL Server Query Analyzer, to create the schema for our new reporting database. We then loaded and executed the accompanying DTS package to transform the Execution Log data and populate the new database tables.

The information contained in the transformed data includes details that can assist us in determining report efficiency and frequency of usage, together with myriad details that assist in resource planning, monitoring the activities of users, and so forth. Specific data items that we can obtain from our Execution Log database include:

• The name of the Report Server Instance that handled a given request;
   

• The Report Identifier;
   

• The User Identifier;
   

• The Request Type (either User or System);
   

• The Rendering Format of the report;
   

• Any Parameter values used for the execution of a given report;
   

• The Start and Stop times of a given report process, from which duration is derived;
   

• The Percentage of Processing Duration spent retrieving the data, processing the report, and rendering the report;
   

• The Source (including Live, Cache, Snapshot, or History) of report execution;
   

• Status of Processing for a given report (indicating either a successful process, or the code of the first Error Condition encountered);
   

• The size of each generated report.
   

• The number of rows returned from query(ies) underlying each report.

Hands-On Procedure

Before we can work with the sample reports, we need to take several preparatory steps to establish connectivity. As part of our setup, we will create a Data Source Connection, then upload the report files we have relocated to a convenient location, as well as performing various read, and other, actions with the sample reports. We will be modifying a copy of one of the reports, to customize it to fit a specific consumer need, as a means for getting some practice in creating new capabilities within our report set, as well as performing other actions within Reporting Services.

To complete the procedures undertaken 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 when we will be copying the sample report files to our local drive.  

Preparation

Create a Folder and Populate it with the Components We Will Use

To prepare for our exercises with the Execution Log sample reports, we will create a folder to house the reports, along with other objects that we will need to complete our practice exercises. The folder can be created anywhere on the PC that is convenient from a local perspective.

1. Right-click Start.

2. Select Explore to launch Windows Explorer.

3. Navigate to a convenient place to create the folder that will contain the “collateral” for our practice session.

4. Create a folder named as follows:

SSP-RS002

The folder appears similar to that depicted in Figure 2.

Figure 2: New Folder in Place to House Lesson Components

5. Locate the Reporting Services installation CD on an accessible CD drive.