About the Series …
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
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
About the Black Belt Articles …
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.
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.
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
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.
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:
details about the new Performance
Dashboard for MSSQL Server, as introduced in Part I of this article;
individual, supporting drillthrough reports that, together with the primary
Dashboard, comprise the Performance Dashboard for MSSQL Server;
report by name and file name, exposing the means of access
to the report, together with its general purpose and other descriptive
Performance Dashboard for Microsoft SQL Server: The Reports
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:
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);
access to the server.
The dashboard reports never perform any system modifications;
requirement to install any binaries (extended stored procedures, CLR assemblies) on the
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;
efficient display of information necessary to identify the most
frequently observed performance problems;
administrators and other consumers in increasing their familiarity with the
wealth of information available in the SQL Server 2005 dynamic management
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.
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.
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).
Click the Start
button on the PC.
SQL Server 2005 within the Program group of the menu.
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.
Engine in the Server type selector.
Type / select
the server name (server name / instance, if appropriate) in the Server name
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.
Preparing to Connect to the Server …
Click the Connect
button to connect with the specified SQL Server Database Engine
The SQL Server
Management Studio opens.
Examine and Overview Performance Dashboard Reports
and Examine the Primary Dashboard Report with the Custom Reports Functionality
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.
the appropriate server instance within the Object Browser.
… 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.
_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 …
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.
this point, we will consider examples of the drillthrough reports underlying
the Performance Dashboard, briefly explaining the purpose of each as we