Tag Archives: report

Old Blog Post: SQL Server 2005 Performance Dashboard Reports

Originally Posted on December 17, 2007

Let me start off by saying this is by no means new. Microsoft released SQL Server 2005 Performance Dashboard Reports back in early March of 2007. It took me until now to stumble across the tool, mainly because I’ve been out of the thick of things from a benchmark perspective. I spent the better half of the day playing with the report. It’s quit impressive and easy to configure.

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

You have to start by installing the add-on. It takes about 20 seconds to install. Once you have run the installer file, go to the directory in which the installer is placed. From there you will find a sql script called setup.sql. Run this against the SQL Server database you want to report. The instructions are a little misleading. They appear to make it seem like you have to run this for every schema in your 2005 instance. That’s not the case. It’s only for every named instance you have installed. From the same directory open the performance_dashboard_main.rdl file. It will format into an XML file. Close that file and you are now ready to play with the Dashboard. To open the Dashboard, open SQL Server Management Studio. Right mouse click on the named instance. From here, select Reports followed by Custom Reports. If you navigate to your install directory, you will see the performance_dashboard_main.rdl file again. Open this and viola you have your report.

Check-out this article for screen shots.

Start with this article from William Pearson. He breaks down each and every aspect of the report. Another article from Brad McGehee on SQL-Server-Performance.com is not as descriptive as the first article, but is pretty good. While I was on the SQL-Server-Performance.com site I came across other links worth taking a look at.
Other Interesting Links

* SQL Server 2005 Waits and Queues
* DBCC SHOWCONTIG Improvements in SQL Server 2005 and comparisons to SQL Server 2000
* Troubleshooting Performance Problems in SQL Server 2005
* Script Repository: SQL Server 2005
* Top 10 Hidden Gems in SQL Server 2005
* Top SQL Server 2005 Performance Issues for OLTP Applications
* Storage Top 10 Best Practices

Advertisements