Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 16, 2007

Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part II

By William Pearson

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 (DMV’s) as data sources, providing the wealth of data the DMV’s 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:

  • Review select details about the new Performance Dashboard for MSSQL Server, as introduced in Part I of this article;
  • Explore the individual, supporting drillthrough reports that, together with the primary Dashboard, comprise the Performance Dashboard for MSSQL Server;
  • Identify each report by name and file name, exposing the means of access to the report, together with its general purpose and other descriptive details.

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:

  • Zero system impact when the dashboard is not being used.  The dashboard uses no background agents or jobs, and the only time there is any system impact is when a user actually runs a report (at which time Management Studio runs the queries specified in the report definition as required to render the report);
  • Read-only access to the server.  The dashboard reports never perform any system modifications;
  • No requirement to install any binaries (extended stored procedures, CLR assemblies) on the server;
  • No requirement to create any special database/tables to store trending or historical information.  The reports do require a one-time installation of some procedures and functions in the msdb database, with which we got hands-on practice in the installation we undertook in Performance Dashboard for Microsoft SQL Server, Part I;
  • The efficient display of information necessary to identify the most frequently observed performance problems;
  • Assisting administrators and other consumers in increasing their familiarity with the wealth of information available in the SQL Server 2005 dynamic management views (“DMVs”).

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.

Illustration 1: Opening SQL Server Management Studio

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.

Illustration 2: Preparing to Connect to the Server ...

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.

Illustration 3: Select Reports -> Performance _dashboard_main ...

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.

Illustration 4: Click Run to Open / Execute the Dashboard Report

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.

Illustration 5: The Performance Dashboard within a New Tab

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.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM