Making the Most Out of the SQL Server 2005 Performance Dashboard

If you have been using SQL Server 2005 for a while now, you may wonder what I am talking about when I refer to the SQL Server 2005 Performance Dashboard. No, you are not suffering from overwork and fatigue, causing you to lose your mind and forget what new features have been included with SQL Server 2005. The SQL Server 2005 Performance Dashboard is a new add-on to SQL Server 2005 that became available shortly after the release of Service Pack 2 for SQL Server 2005.

In brief, the SQL Server 2005 Dashboard is a custom report (custom reports are a new feature of Service Pack 2) for Management Studio that gathers data from the many Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs) available in SQL Server 2005. It is not a replacement for other performance tools, but an adjunct tool to help DBAs better identify and troubleshoot performance problems. In many ways, the SQL Server 2005 Dashboard reports are similar to the many built-in reports already offered by Management Studio.

Because the SQL Server 2005 Performance Dashboard is an add-on tool, you must take the time to download the free tool from Microsoft’s Web site and install it. Fortunately, this is an easy process, and I highly recommend that all DBAs download and install the Performance Dashboard on every SQL Server 2005 instance that has Service Pack 2 installed.

Where to Get the SQL Server 2005 Performance Dashboard?

In order to install the SQL Server 2005 Dashboard, you must download at least two files from Microsoft. First, if you have not done so already, you must download SQL Server 2005 Service Pack 2. This is because Service Pack 2 includes new functionality that has been added to support the Performance Dashboard.

You can download SQL Server 2005 Service Pack 2 from:

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx

You can download the SQL Server 2005 Performance Dashboard add-on from:

http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

How Do You Install the SQL Server 2005 Dashboard?

Before you can install the SQL Server 2005 Dashboard, you must first install Service Pack 2. Yes, you are right, maybe you haven’t upgraded to Service Pack 2 yet. In that case, you will have to wait until you upgrade before you can install the Performance Dashboard. There are several reasons why you must first install Service Pack 2, some of which include the addition of the custom report feature to Management Studio, added server-side functionality, and bug fixes.

The Performance Dashboard custom reports don’t require you to install Reporting Services on each SQL Server. The custom reports run under the client-side report viewer control, which is a part of Management Studio. This control allows you to run custom reports, but not to create them. If you want to create custom reports, you can, but you will need the SQL Server Business Intelligence Development Studio to do this.

Assuming you have installed Service Pack 2, and you have downloaded the Performance Dashboard add-on (SQLServer2005_PerformanceDashboard.msi), here is how you install it.

1. Double-click on SQLServer2005_PerformanceDashboard.msi to begin the installation, and you see the above window. Click Next to proceed.

2. Accept the license agreement and click Next.

3. Enter the registration information and click Next.

4. Before you automatically accept the default installation path for storing the reports that make up the Performance Dashboard, you may want to consider another option. And that is to store the reports on the share of a file server instead of the local server. Why would you want to do this? While it is not required, the benefit of doing this is that all your SQL Server instances can share this same shared folder to access the reports, which means you won’t have to install the Performance Reports on each and every SQL Server instance. This also can come in handy if you create custom reports yourself and want to easily share them among all SQL Server instances. While there is no requirement that the report definition files for Performance Dashboard have to be located on each SQL Server instance, it is important that all of the files be located in the same folder. In this example I will use the default folder. Once you have selected the folder location, click Next.

5. Now you are ready to install the Performance Dashboard, so click Install. After a very quick installation, you see the final dialog box.

6. Click Finish. Now, I bet you think you are done and ready to begin using the Performance Dashboard. Nope, there is one more step you need to do first before you can begin using the Performance Dashboard.

7. In the folder where the Performance Dashboard report definitions were installed, you will find a script called: setup.sql. You will need to run this script in every instance of SQL Server 2005, SP2, where you want to use the Performance Dashboard.

Above is a screen shot of the folder that was created when you installed the Performance Dashboard. There are several things to note in this folder. First, is the setup.sql script already described. Second, the PerfDash.chm file, which is the help file for the Performance Dashboard. I recommend that you read this help file, as it is very useful. Third, note the performance_dashboard_main.rdl report definition file. This is the key report file that you will need to access when you begin the Performance Dashboard. We will talk more about this later. The rest of the .rdl files are sub-reports of the performance_dashboard_main.rdl report definition file and cannot be used directly. You may also note that there are no binaries in this folder. This is because installing the Performance Dashboard does not install any binaries in SQL Server. Other than the custom report definitions and the setup.sql script, there are no other components of the Performance Dashboard.

8. To run the setup.sql script, launch Management Studio, load the script, and run it. A partial view of the script is shown below.

This script runs in the context of the msdb database and creates a variety of functions and stored procedures used to create the data supplied by the Performance Dashboard. No new tables or databases are created by this script. If you like to play with code, this script is a great example of how you can take advantage of the power of DMVs and DMFs. Be sure you run this script in every instance. This, unfortunately, is a manual process.

You are now done with the initial setup of the Performance Dashboard, and it is ready for use.

Continues…

Leave a comment

Your email address will not be published.