Optimizing Microsoft SQL Server 2000 Reporting Services: Performance and Access Reports from the Execution Log
About the Series
This is the second article of the series, Optimizing Microsoft SQL Server Reporting Services, which appears concurrently with my other series. The primary focus of this series is the introduction of optimization concepts and practices for MSSQL Server Reporting Services (“Reporting Services”). The series is designed to provide hands-on application of the fundamentals of Reporting Services optimization from multiple perspectives.
As we progress through the series, we may reference previous articles and the concepts we have introduced therein. However, one of my objectives is to make each article as “standalone” as possible, meaning that we should not encounter cases where we cannot complete a given procedure without components or objects that we have created in previous articles. This should make it easier for “casual” visitors to join us with any article, and still successfully complete that session, given an existing understanding of the concepts and principles that we have accumulated up to that point.
In this series of articles, I’ll share some of my insights and discoveries as I continue to implement Reporting Services among my diverse client base, in hopes of shedding more light on the practical realities I find in the optimization arena. I’ll do this from the viewpoint of a practitioner who has worked closely and repeatedly with many BI products, sometimes comparing functionalities between some of these and the new Reporting Services offerings. For more information about the series itself, and for a summary of the reasons behind my becoming a subject matter evangelist for Reporting Services, which I am confident will commoditize Business Intelligence in general, see “Introduction to the Series” within the first article of this series, Execution Log Reporting: Preparation as a Data Source.
In our previous article, Execution Log Reporting: Preparation as a Data Source, we began with a discussion about a valuable source of information for performance and auditing analysis, the Report Server Execution Log. We noted that the Execution Log captures data specific to individual reports, including when a given report was run, identification of the user who ran it, delivery destination of the report, and which rendering format was used, among other information.
After discussing the nature of Execution Logging in general, we touched upon several of the ways in which it can assist us in understanding the performance of our reports, the actions of users, and a host of other details about the reports we create in Reporting Services. Working within a practice example where we responded to the expressed business needs of a hypothetical group of information consumers, we then performed transformation of the data in the Execution Log to a user-friendly reporting data source. We used the tools provided as samples with the Reporting Services installation to create and populate a MSSQL Server database, noting several of the benefits that would accrue to the information consumers. For the detailed steps we undertook, and to prepare to accomplish the steps of this article, please see Execution Log Reporting: Preparation as a Data Source.
Our focus in this article will be an examination of some of the uses to which the new Execution Log database might be put. Our examination will consist of hands-on publication of the sample reports provided with Reporting Services as a “starter set;” and then go beyond that set and create a customized report to show the ease with which we might help the information consumers we support to meet general and specific needs. We will propose other considerations that will add value to this already rich resource, and discuss ways in which we can leverage Execution Log reporting to make us better report writers from multiple perspectives.
In this session we will:
Review the hypothetical business requirements behind the procedures in this and the preceding article;
Review the Execution Log reporting database we created in our last lesson, focusing on its schema and the information it contains;
Review possible value-adds that come with Execution Log reporting;
Make copies of the sample report set provided with Reporting Services in a convenient location;
Upload the sample report copies, for execution within Report Manager;
Customize a copy of a sample report, to meet extended business requirements from our hypothetical information consumers group.
Execution Log Performance and Access Reports
Considerations and Comments
As a scenario for our practice examples in both our previous and current articles, we established a Reporting Services business requirement of a group of information consumers at a hypothetical client location. The consumers expressed the need to monitor report activity, for purposes of performance and auditing evaluation, for their recently implemented Reporting Services installation. The consumers asked that we work with them to create a database that is populated and maintained with this data for many potential uses. Upon accepting the project, we informed the group that, while we had created numerous relatively elaborate databases of this sort for clients in the past, our initial pilot in this instance would include Execution Log reporting capabilities that could be implemented rapidly and easily, using tools that accompany the installation of Reporting Services.
We made it clear that we would likely expand the capabilities to many other measures for overall performance and auditing monitoring after our pilot. We explained that beginning with the prefabricated “starter set” would be efficient, because Reporting Services provided a script to construct a basic reporting database to house the Execution Log data that it generates. In addition to the creation script, a DTS package, for transformation of the data, and for subsequently populating (and prospectively updating) the new database, was also included. Finally, a set of basic sample reports were included in the same “extras” bundle, which we could run as soon as we had transformed data from the Execution Log to the newly created database.
We pointed out that we had found this approach to be a “quick win” with regard to getting basic functionality in place; we also indicated that this was a great time in the Reporting Services implementation to be making performance and audit analysis capabilities available – just as we were beginning to write our first enterprise reports. The sample reports would serve several complementary, value-added functions, including:
Provision of a means for the Administrator to immediately see who was performing what actions within the new system;
Provision of an excellent learning tool for new report authors, whereby they could obtain confirmation that their new reports had executed;
Presentation of basic report samples, upon which the ultimate users could base requests for customization. (This could save a great deal of time in obtaining the requirements from users who, although they knew they needed these reports, may not have known “where to start” in providing a useful specification for their design.);
Facilitation of optimization of reports and their underlying queries, both at times during and after creation, and at various points in time as a part of ongoing system upkeep and maintenance (tuning could be suggested, for example, as data sources grew in size, as the number of information consumers increased, as security needs changed, and so forth.
With the Execution Log data source now in place, we are ready to proceed with aligning and publishing the sample report set to kick off the new reporting efforts. Included on the Reporting Services installation CD is a group of sample Execution Log reports. These reports include the following titles:
Longest Running Reports
Reports by Month
Reports by User
Reports Executed by Day
Report Success Rate