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

By William Pearson

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.

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