Diagnose performance problems in
MSSQL Server 2005 with a powerful new administration tool. BI Architect Bill Pearson provides hands-on guidance in installing and
using this powerful new dashboard / report pack, freely available to MSSQL
Server 2005 users everywhere.
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 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
About the Black Belt Articles ...
As I state
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 many
real world scenarios, as most of us are aware, these roles often intersect.
this article, we extend the meaning of samples, as well as the type of
Administrator, a bit further. We use an installation file recently
made available by Microsoft that allows us to implement an exciting new report
pack with minimal effort. Because we will be running the installation package,
certain system and permissions requirements apply. We will cover these in the Preparation
more information about the BlackBelt articles, see the section
entitled About the BlackBelt Articles in BlackBelt
Components: Manage Nulls in OLAP Reports.
On the heels of the release of Service Pack 2 for MSSQL
Server 2005 comes what will, perhaps, be the most warmly received Reporting
Services report pack to date, the Performance Dashboard for Microsoft
SQL Server (Performance Dashboard Reports). The Performance
Dashboard Reports are a set of custom report files designed to be
run from within SQL Server Management Studio.
The Performance Dashboard Reports are targeted
toward SQL Server Administrators and other users; the objective of the report
set is to act as both a health monitoring and diagnostic tool. Although it
relies upon Reporting Services definition files (.rdl), Reporting
Services does not need to be installed to use the Performance Dashboard
Reports. This custom report set relies upon SQL Server's dynamic
management views (DMVs) as a data source, providing the wealth of data the
dynamic management views 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.
report set comes with a primary dashboard report file, as we shall see in the
hands-on installation procedure that follows. This report file is loaded
directly as a custom report in SQL Server Management Studio.
The other Performance Dashboard Reports are
accessed via the Reporting Services drillthrough mechanism, each path of
which is initially entered when the user clicks a navigation link on the main
page. The linkages are pre-constructed, and, once the primary 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.
NOTE: For more information on Custom Reports, which
debuted with Analysis Services 2005, Service Pack 2, see my
article Administration and Optimization: Introducing Custom Reports,
a member of the Database Journal Introduction to MSSQL Server Analysis Services series.
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 concerns. As an
example, we can identify the presence of a bottleneck on a given server,
and then investigate the details surrounding the bottleneck, capturing
diagnostic data along the way to allow us to eliminate the problem, or to
mitigate the condition by taking indicated actions (perhaps until we can get to
a point where we can implement a better design or remove conflicting resource
demands, etc.). An example of a commonly encountered challenge might be the
identification of significant system waits for disk IO: we can
access the dashboard, and subsequently drill to various reports as appropriate,
to ascertain the identity of the most resource-hungry sessions, the queries
that make up those sessions, and even the query plan associated with each
addition to (and in conjunction with) CPU and IO bottlenecks (and
which queries are consuming the most resources), typical performance problems
whose resolution we might pursue with the Performance Dashboard Reports include:
recommendations generated by the query optimizer (missing indexes);
NOTE: We discuss the individual, underlying members of the Performance Dashboard Reports, and more
specific details of drillthrough operation, in Part
II of this article.
In Part I and Part
II of this article, we will implement the Performance Dashboard
Reports, and then take them on a test-drive to gain an appreciation
for the fact that, for a tiny investment in time to install them, we can
experience significant returns through the performance enhancing information
that the Performance Dashboard Reports provide. As a part of our examination of the Performance Dashboard Reports in this article, we will:
discuss the new Performance
Dashboard for MSSQL Server;
installation of the Performance Dashboard Reports by downloading and
running the installation file (.msi) available from Microsoft;
system requirements are met for installing the Performance Dashboard Reports;
remaining setup steps within SQL Server Management Studio;
examine the primary dashboard report within the new Management Studio Custom
Look ahead to
our examination of the individual, supporting drillthrough reports in Part II of this article.
Dashboard for Microsoft SQL Server
shall see in the Preparation section of our installation
procedure, minimal (but important) system requirements must be met for
installing and using Performance Dashboard Reports. We will touch upon
these, and illuminate some of the unclear points in the rather sketchy
documentation that accompanies the report pack, in the steps of the
installation that we undertake below.
of a set of .rdl files, a setup script (.sql), and a help file, there are not
many decisions to be made or other showstopper considerations, beyond the
basic system requirements. One thing to think through is the location
where we wish to store the report and help files we have great flexibility
here, beyond the requirement that the .rdl files must reside in the same
directory. (As we have already noted, we are not required to have Reporting
Services running on the server involved.)
numerous recommendations, surrounding deployment and maintenance in the
provided help file (PerfDash.chm) that bear noting, not just for the
installation and use of the Performance Dashboard Reports, but for the
use of Custom Reports in general, among other items to place in a
growing best practices file. Examples include a suggestion that we create shares
to contain the enterprises Custom Reports. Moreover, the suggestion
is offered that we set up a subdirectory within the shared Custom
Reports directory to specifically house the Performance Dashboard
Reports files, so as to avoid name collisions with any other Custom
Reports that we may install later. (We perform our installation in a manner
that complies with these suggestions in the procedural section that follows).
Other pointers and information, such as tips for easily finding and accessing
the primary dashboard report, after first opening it as a Custom Report,
are included, as well, in Part II.
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: Download and Run the Installation
File for SQL Server 2005 Performance Dashboard Reports
The installation file for the Performance Dashboard Reports can be downloaded from the Microsoft
installation package (SQLServer2005_PerformanceDashboard.msi) from the Download
Center to a convenient location on the server / network, as shown in Illustration 1.
Illustration 1: Download
the Installation Package to a Convenient Location ...
Once the file
is downloaded, right click the file in Windows Explorer.
from the context menu that appears, as depicted in Illustration 2.
Illustration 2: Installing
the Performance Dashboard Files
Click Run on
the Open File Security Warning dialog that appears next, as shown in Illustration 3.
Illustration 3: Running
the Installation ...
Click Next on
the Welcome page of the Install Wizard for Microsoft SQL Server 2005
Performance Dashboard Reports that next appears, as shown in Illustration
4: The Welcome Page of the Install Wizard
Read and agree
to the terms of the license agreement by clicking the top radio button, before
again clicking the Next button, as depicted in Illustration 5.
5: License Agreement Page of the Wizard
information requested in the Registration Information page that appears
at this point, and then click the Next button once again, as shown in Illustration
6: Complete the Registration Information
On the Feature
Selection page that appears next, click the Browse button to the right
of the default installation path, which appears below the selection list.
we mentioned earlier that it doesnt matter where we locate the report set, as
far as system requirements go (as long as we place them in the same
directory), we will follow a best practice outlined in some of the
documentation, where Microsoft suggests that we create a subdirectory
specifically for the dashboard files mainly, as we have noted earlier, to help
in the avoidance of name collisions with any other Custom Reports that we may install later. We are placing them
within the Custom Reports directory, as well, to support the
implementation of another suggested general practice: to create a share
which will contain all the enterprises Custom Reports.
(If you have a more convenient / preferable place to put them within the
context of your local environment, please do so in the following steps).
When the Change
Current Destination Folder dialog opens, navigate to the SQL Server
Management Studio folder (it is in a default path similar to the
following: C:\Documents and Settings\WEP3\My Documents\SQL Server
the Custom Reports folder that appears, as depicted in Illustration 7.
7: Navigating into the Custom Reports Folder
the Custom Reports folder, click the Create New Folder button to
the right of the selector labeled Look in, atop the Change Current
Destination Folder dialog, shown circled in Illustration 8.
8: Click the Create New Folder Button ...
following into the caption field of the newly created folder (currently titled New
Current Destination Folder dialog appears, with the newly created folder,
as depicted in Illustration 9.
9: The Change Current Destination Folder with New Destination Folder ...
to accept the newly assigned folder, and to dismiss the Change Current
Destination Folder dialog.
On the Feature
Selection page, to which we are once again returned, click the icon
(labeled Performance Dashboard, with the downward pointing arrow) to
reveal the selector list.
feature, and all subfeatures, will be installed on local hard drive, via
the right dropdown arrow, within the selection list of the page, as shown in Illustration
10: Feature Selection Setting ...
to save our input and continue.
arrive at the Ready to Install the Program page of the Setup Wizard.
to begin installation, as depicted in Illustration 11.
11: Ready to Install the Program Page ...
begins, and then the Completing the Microsoft SQL Server 2005 Performance
Dashboards Setup page appears, as shown in Illustration 12.
12: Completing the Performance Dashboards Setup Page ...
Setup Wizard concludes and closes. The Performance Dashboard Reports set, along with the other files that we mentioned earlier, have been
placed in the subdirectory we have dictated, but a few steps remain to put the
report pack into action. We will take those steps from within MSSQL Server Management Studio in the section that follows.
Ascertain that System Requirements are Met
For purposes of our installation,
we will enter the MSSQL
Server Management Studio,
from which we will discuss the system requirements in some cases
verifying compliance from inside the Management Studio. Obviously, we
would likely ascertain that we met requirements before even beginning to
install software; we do so in the manner Ive described simply to both mention
the requirements and to provide a quick means of ascertaining compliance
enroute to the objective, such as we might want to perform when we have to
rely upon the assertions of others, assumptions that predecessors have
installed performed prerequisites, etc.
Click the Start
button on the PC.
SQL Server 2005 within the Program group of the menu.
Server Management Studio, as depicted in Illustration 13.
Illustration 13: 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 14.
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.
We can quickly check our
readiness at this point with at least the most important system requirement
the requirement that the SQL Server instance being monitored via the Performance
Dashboard Reports must also be running Service Pack 2 or
The requirement is in
place partially because Performance Dashboard Reports is a
collection of Custom Reports designed to be hosted in SQL Server
Management Studio, and since the Custom Reports feature is new in Service
Pack 2, we must have performed the Service Pack 2 update to be able
to use them. Moreover, a client machine running the reports must be upgraded
to the Service Pack 2 version of the client tools (Management Studio).
Other Service Pack 2
functionality upon which the Performance Dashboard Reports rely includes:
to the OBJECT_NAME() function to accept an optional dbid parameter;
fixes related to the data reported in the dynamic management views
Dashboard Reports check the SQL Server version and will
return an error if the version requirements are not met. SQL Server 2005
makes it easy to determine, from our present position within SQL Server
Management Studio, whether Service Pack 2 has been installed. The
most obvious indication is in the version number (it should be Version
9.00.3042.01 or greater) that we can see both
in the Server description atop Solution Explorer, and in the Solution
Explorer Details tab to the right of the Solution Explorer pane, as
represented in composite Illustration 15.
Illustration 15: Two
Easily Verified Version Numbers in Management Studio (Composite View)
Its use of the Custom Reports feature that debuts in Service
Pack 2 means that SQL Server Management Studio relies upon the client side
report viewer control to present the Performance Dashboard Reports.
As we have already noted, this means that Reporting Services does
not have to be installed in the environment involved. Other requirements, such
as the specification that the computer running SQL Server 2005 must
use a fixed processor frequency, exist, and can be reviewed in the help
file that accompanies the Performance Dashboard Reports installation.
Once the installation
package has been run, we are ready to run the Setup file that can be
found in the same directory in which we placed the reports. We can then open and
run the primary dashboard report with the new Custom Reports option
within SQL Server Management Studio. We will take these final setup
steps in the section that follows.
Perform Remaining Setup Steps within SQL Server Management Studio
Once the installation
package has been run, we are ready to run the Setup file that can be
found in the same directory into which we placed the reports. We can then open
the primary dashboard report with the new Custom Reports option within SQL
Server Management Studio. We will take these final setup steps in the
sections that follow.
the Provided Setup Script against the Desired Server Instance
We can easily run the
necessary SQL Setup script included with the Performance
Dashboard Reports from our immediate position within the SQL
Server Management Studio.
-> Open from the main menu within SQL Server Management Studio.
from the cascading menu that appears, as shown in Illustration 16.
Illustration 16: Select
Open -> File ...
the Performance Dashboard subdirectory that we created earlier (within
the Custom Reports directory), which we populated with the Performance Dashboard Reports as a part of running the
Click the setup.sql
script file, as depicted in
Illustration 17: Select
Click Open to place the
script into a newly opening query tab of the Management Studio.
The new tab, containing
(and named after) the setup script, appears as partially shown in
Illustration 18: Setup
Script Open in Management Studio Tab (Partial View)
Click the Execute
(!) button in the Management
as depicted in Illustration 19.
Illustration 19: Execute
the Query ...
The query executes, and
the Command(s) completed successfully message appears in the Message
pane, underneath the Query pane, as shown in Illustration 20.
Illustration 20: Successful
Completion of Query is Indicated ...
has set up a new schema, called MS_PerfDashboard, in the MSDB
database, together with a set of functions and procedures referenced
by the Performance
Dashboard Reports. It is important to remember
that the Performance
rely solely upon information
made available in the SQL Server 2005 dynamic management views (DMVs),
and that no databases / tables are created (as is the case with standard Reporting
Services reports), or used to store trending or other data for use by the
Examine the Primary Dashboard Report with the Custom Reports Functionality
remains to complete setup is to open our primary dashboard report using the new
Custom Reports functionality that is introduced, as we have seen, within
Service Pack 2.
Right-click the appropriate
server instance within the Object Browser.
Select Reports ... from
the context menu that appears.
Select Custom Reports ... from
the cascading menu that appears next, as depicted in Illustration 21.
Illustration 21: Select Reports
-> Custom Reports ...
Navigate to the Performance
Dashboard subdirectory we created earlier, and select the performance_dashboard_main.rdl
file contained therein, within the Open File dialog, as shown in Illustration 22.
Illustration 22: Select
the Primary Dashboard Report File ...
Click Open to open the
file within Custom Reports.
As we shall
discuss in Part II of this article,
the first time we choose the Custom Report option, we are prompted to
choose the report to run, as we see here. Once we have browsed to the
share housing the primary dashboard report file, and loaded it into the viewer,
the dashboard can be accessed prospectively simply by using the recent file
list, instead of manually browsing to the share.
Click Run on the Run
Custom Report dialog that appears next, as depicted in Illustration 23.
Illustration 23: Click
Run to Execute the Report ...
message Retrieving Data briefly appears, and then the dashboard opens,
presenting status information for this point in time (for purposes of this
exercise, I am performing a full process of the Adventure Works Analysis
Services database, as well as a simultaneous full backup of a copy of the Adventure
Works database), as shown in Illustration 24.
Illustration 24: The
Performance Dashboard in Action ...
point, we can drill through to various supporting reports, via the hyperlinks
that appear on the dashboard. We will explore the set of supporting reports in
the subsequent article in this series. In the meantime, if you wish to test
drillthrough and other features, this is a great time to become familiar with
all the parts and pieces.
drillthrough to underlying support reports, as desired, by clicking the
respective hyperlinks in the Performance Dashboard.
-> Exit, when ready, to leave SQL Server Management Studio, as depicted in Illustration 25.
Illustration 25: Select
the Primary Dashboard Report File ...
loaded the primary dashboard report as a Custom Report within Management
Studio, we can easily open and refresh it (and, of course, access all
supporting drillthrough reports from it) anytime we enter Management Studio.
In our next article we will examine the ease with which gaining such access is
possible, as well as review the purposes and uses of the supporting report files
that ship with the Performance Dashboard Reports, together with
other topics related to this useful new administrative tool.
In this article, we introduced the Performance Dashboard for MSSQL Server, a new, useful administrative report pack
offered for download by Microsoft just after the release of MSSQL Server 2005 Service Pack 2. After
discussing some of the details of the contents within, and valuable uses for,
this new reporting option, we prepared for installation by downloading and
running the installation
file (.msi) available from Microsoft. As an additional part of
preparation, we ascertained that system requirements were met for installing
the Performance Dashboard Reports in our local environments.
We next performed
remaining setup steps within SQL Server Management Studio, including
taking the steps to load the primary dashboard report within the new Management Studio
Custom Reports functionality. We examined the face of the dashboard within
SQL Server Management Studio. Finally, we looked ahead to our examination of the individual,
supporting drillthrough reports in Part II of this article.
See All Articles by Columnist William E. Pearson, III
Discuss this article in the MSSQL Server 2000 Reporting Services Forum.