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

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.

Overview

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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles