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

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 many
real world scenarios, as most of us are aware, these roles often intersect.

In
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
section below.

For
more information about the BlackBelt articles, see the section
entitled “About the BlackBelt Articles” in BlackBelt
Components: Manage Nulls in OLAP Reports
.

Overview

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
(DMV’s) 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.

The
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.

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 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
constituent statement.

In
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:

  • Index
    recommendations generated by the query optimizer (missing indexes);

  • Blocking;

  • Latch
    contention;

  • Others.

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:

  • Introduce and
    discuss the new Performance
    Dashboard for MSSQL Server
    ;

  • Prepare for
    installation of the Performance Dashboard Reports by downloading and
    running the installation file (.msi) available from Microsoft;

  • Ascertain that
    system requirements are met for installing the Performance Dashboard Reports;

  • Perform
    remaining setup steps within SQL Server Management Studio;

  • Open and
    examine the primary dashboard report within the new Management Studio Custom
    Reports
    functionality;

  • Look ahead to
    our examination of the individual, supporting drillthrough reports in Part II of this article.
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