Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II
April 16, 2007
About the Series ...
This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services (Reporting Services), presenting an overview of its features, with tips and techniques for real-world use. For more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools needed to complete the hands-on portion of this article, see BlackBelt Administration: Linked Reports in Report Manager, another article within this series.
About the Black Belt Articles ...
As I state in BlackBelt Components: Manage Nulls in OLAP Reports and other articles of this subseries, the Black Belt articles represent an attempt to minimize the setup required in simply getting to a point within an article where we can actually perform hands-on practice with the component(s) or method(s) under consideration. I typically accomplish this by using existing report samples or other prefabricated objects that either come along as part of the installation of the applications involved, or that are otherwise readily accessible to virtually any organization that has installed the related components of the Microsoft business intelligence solution. In the Black Belt Administration articles, I focus upon procedures and tools that specifically relate to the job of the Reporting Services Administrator, versus report authors and other developers, although in real world scenarios, these roles often intersect.
For more information about the BlackBelt articles, see the section entitled About the BlackBelt Articles in BlackBelt Components: Manage Nulls in OLAP Reports.
In this article, we extend the meaning of samples, as well as the definition of Administrator, a bit further. (The Performance Dashboard can serve as an excellent tool for DBAs, as well as other administrators within the various layers of the integrated Microsoft BI solution.) In our last article, Performance Dashboard for Microsoft SQL Server, Part I, we ran the installation package provided by Microsoft to install the integrated reports comprising the Performance Dashboard. To gain the most benefit from this article, the installation steps we undertook should have been performed.
In Performance Dashboard for Microsoft SQL Server, Part I, we introduced the Performance Dashboard for Microsoft SQL Server (Performance Dashboard Reports). We noted that this report pack, made available on the heels of the release of Service Pack 2 for MSSQL Server 2005 will, perhaps, be the most warmly received Reporting Services report set of its type to date. We noted that the Performance Dashboard Reports are a set of report files designed to be run from within SQL Server Management Studio. Targeted toward MSSQL Server Database Administrators and other users; the objective of the report set is to act as both a health monitoring and diagnostic tool.
We mentioned that, although the Performance Dashboard Reports set relies upon Reporting Services definition files (.rdl), Reporting Services does not need to be installed to use this custom report set. The Performance Dashboard and its child reports rely upon SQL Server's dynamic management views (DMVs) as data sources, providing the wealth of data the DMVs contain, while insulating the viewers of the information from the views and the structures underlying them. No additional sources, data capture or tracing is required to access and use this storehouse of performance information. Other obvious benefits of using these prefabricated views are constant availability of the information they contain and their inexpensive nature (from the tandem perspective of collection and querying) as a source of server monitoring.
As a part of our introduction in Performance Dashboard for Microsoft SQL Server, Part I, we further mentioned that the report set comes with a primary dashboard report file, which we loaded directly as a custom report in SQL Server Management Studio as part of our hands-on installation procedure. At that time, we noted that the linkages between the Dashboard report and the underlying drillthrough reports are pre-constructed, and that, once the dashboard report is loaded as a Custom Report in Management Studio, the rest of the reports work out of the box automatically, without any additional setup. The end effect is that the primary dashboard gives a summarized status overview, while serving as a multi-directional launch point from which we can drill into the specific details we need to diagnose problems, inefficiencies, and other subjects of analysis and reporting with which we develop interests or concerns.
In this article, we will overview the individual, underlying members of the Performance Dashboard Reports, and more specific details of the drillthrough operation through which we reach them. Along the way, we will summarize the general purposes for each report as intended by its authors. As a part of our continued examination of the Performance Dashboard Reports, we will:
Performance Dashboard for Microsoft SQL Server: The Reports
Extending well beyond earlier report pack releases, which focus more upon providing guided samples in adapting Reporting Services to various Microsoft applications and databases, Performance Dashboard was designed as a set of custom reports to be hosted within SQL Server Management Studio. The reports were written to deliver information from a consistent and familiar tool already used by database administrators. According to the documentation, there were several key design goals for this effort:
As we noted in the Preparation section of our installation procedure in Performance Dashboard for Microsoft SQL Server, Part I, minimal (but important) system requirements must be met for installing and using Performance Dashboard Reports. We touched upon these, illuminating some of the unclear points in the rather sketchy documentation that accompanies the report set, in the steps of the installation that we undertook.
NOTE: To receive the most benefit from this article, you should first install the Performance Dashboard Reports that form its focus. An expanded, step-by-step installation is provided in Performance Dashboard for Microsoft SQL Server, Part I.
We will perform our practice session from inside the MSSQL Server Management Studio. For more exposure to the Management Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see other articles in this series, as well as within my Database Journal series Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to keep to the focus of the article more efficiently.
Preparation: Open MSSQL Server Management Studio and Execute the Primary Dashboard Report
For purposes of our exploration of the Performance Dashboard Reports, we will enter the MSSQL Server Management Studio. In Performance Dashboard for Microsoft SQL Server, Part I, this was where we performed the initial inspection of our local MSSQL Server installations to ascertain compliance with the relatively minimal system requirements to support the set of custom reports. We installed the primary Dashboard report as a custom report within Management Studio, once we had determined that Service Pack 2 was in place, and that the other requirements were met (demonstrating how to check compliance from within the Management Studio itself, prior to the installation).
1. Click the Start button on the PC.
2. Select Microsoft SQL Server 2005 within the Program group of the menu.
3. Click SQL Server Management Studio, as shown in Illustration 1.
The Connect to Server dialog appears, after the brief Management Studio splash screen.
4. Select Database Engine in the Server type selector.
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. (I have selected Windows Authentication in my own environment.)
The Connect to Server dialog appears similar to that depicted in Illustration 2.
7. Click the Connect button to connect with the specified SQL Server Database Engine server.
The SQL Server Management Studio opens.
Procedure: Examine and Overview Performance Dashboard Reports
Open and Examine the Primary Dashboard Report with the Custom Reports Functionality
As we have noted in Performance Dashboard for Microsoft SQL Server, Part I, we access the Performance Dashboard Reports from the primary Dashboard report. We drill through to the subordinate reports via respectively associated navigation points on the Dashboard. (Because parameters for the subordinate, target reports are passed via the drillthrough mechanism, and because at least one parameter is required to run each of these reports, the subordinate reports must be opened / executed from the Dashboard report.) Recall that we load the Dashboard report as a custom report within SQL Server Management Studio: we will thus begin our exploration of the individual drillthrough reports by opening the report file named performance_dashboard_main.rdl.
1. Right-click the appropriate server instance within the Object Browser.
2. Select Reports ... from the context menu that appears.
The first time we choose the Custom Reports ... option, we are prompted to choose the report to run. In Performance Dashboard for Microsoft SQL Server, Part I, we browsed to the share into which we had placed the performance_dashboard_main.rdl report file (as a part of our installation procedure), where we selected it for loading as a custom report. Assuming we have performed the installation in Part I already, we can access the Dashboard report via the recent file list, at this stage, instead of manually browsing to the share, again as we shall see in the next step.
3. Select performance _dashboard_main from the recent file list section of the cascading menu that appears next, as shown in Illustration 3.
4. Click Run on the Run Custom Report dialog that appears next, warning us that we are about to execute a Custom Report, as depicted in Illustration 4.
We briefly see indication that data is being retrieved, and then the Performance Dashboard opens within a new tab, appearing similar to that shown in Illustration 5.
NOTE: The appearance of the local reports that you examine within this session will differ from those depicted from within my environment (and from the sample report illustrations, as well). Obviously, some details may not appear at all, depending upon the load currently experienced by the server upon which the Performance Dashboard Reports are executed. To stimulate the readings a bit, you might consider executing a few long-running operations (such as a large database import, sample cube processing, etc.) in the background, as we proceed through the steps that follow.
At this point, we will consider examples of the drillthrough reports underlying the Performance Dashboard, briefly explaining the purpose of each as we encounter it.