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 Mar 19, 2007

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

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


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.

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