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

Monday Mar 19th 2007 by William Pearson
Share:

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

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.

Installing Performance Dashboard for Microsoft SQL Server

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

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

There are 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 enterprise’s 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.

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: 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 Download Center.

1.  Download the 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 ...

2.  Once the file is downloaded, right click the file in Windows Explorer.

3.  Select Install from the context menu that appears, as depicted in Illustration 2.


Illustration 2: Installing the Performance Dashboard Files

4.  Click Run on the Open File – Security Warning dialog that appears next, as shown in Illustration 3.


Illustration 3: Running the Installation ...

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


Illustration 4: The Welcome Page of the Install Wizard

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


Illustration 5: License Agreement Page of the Wizard

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


Illustration 6: Complete the Registration Information

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

Although we mentioned earlier that it doesn’t 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 enterprise’s 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).

9.  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 Management Studio.

10.  Double-click the Custom Reports folder that appears, as depicted in Illustration 7.


Illustration 7: Navigating into the Custom Reports Folder

11.  Once inside 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.


Illustration 8: Click the Create New Folder Button ...

12.  Type the following into the caption field of the newly created folder (currently titled New Folder):

Performance Dashboard

The Change Current Destination Folder dialog appears, with the newly created folder, as depicted in Illustration 9.


Illustration 9: The Change Current Destination Folder with New Destination Folder ...

13.  Click OK to accept the newly assigned folder, and to dismiss the Change Current Destination Folder dialog.

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

15.  Select This 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.


Illustration 10: Feature Selection Setting ...

16.  Click Next to save our input and continue.

We arrive at the Ready to Install the Program page of the Setup Wizard.

17.  Click Install to begin installation, as depicted in Illustration 11.


Illustration 11: Ready to Install the Program Page ...

Installation begins, and then the Completing the Microsoft SQL Server 2005 Performance Dashboards Setup page appears, as shown in Illustration 12.


Illustration 12: Completing the Performance Dashboards Setup Page ...

18.  Click Finish.

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

Preparation: 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 I’ve 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.

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 depicted in Illustration 13.


Illustration 13: 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 14.


Illustration 14: 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.

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

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:

  • sys.dm_exec_text_query_plan;
  • an extension to the OBJECT_NAME() function to accept an optional dbid parameter;
  • several bug fixes related to the data reported in the dynamic management views (DMVs).

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

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

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

1.  Select File -> Open from the main menu within SQL Server Management Studio.

2.  Select File... from the cascading menu that appears, as shown in Illustration 16.


Illustration 16: Select Open -> File ...

3.  Navigate to 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 installation package.

4.  Click the setup.sql script file, as depicted in Illustration 17.


Illustration 17: Select Setup.sql ...

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


Illustration 18: Setup Script Open in Management Studio Tab (Partial View)

6.  Click the Execute (!) button in the Management Studio toolbar, 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 ...

The script 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 Dashboard Reports 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 reports.

Open and Examine the Primary Dashboard Report with the Custom Reports Functionality

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

1.  Right-click the appropriate server instance within the Object Browser.

2.  Select Reports ... from the context menu that appears.

3.  Select Custom Reports ... from the cascading menu that appears next, as depicted in Illustration 21.


Illustration 21: Select Reports -> Custom Reports ...

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

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

6.  Click Run on the Run Custom Report dialog that appears next, as depicted in Illustration 23.


Illustration 23: Click Run to Execute the Report ...

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

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

7.  Perform drillthrough to underlying support reports, as desired, by clicking the respective hyperlinks in the Performance Dashboard.

8.  Select File -> Exit, when ready, to leave SQL Server Management Studio, as depicted in Illustration 25.


Illustration 25: Select the Primary Dashboard Report File ...

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

Conclusion ...

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.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved