Introduction
SQL Server 2005 and SQL Server 2008 provide graphical reports for monitoring system health and performance. While there have always been various queries, stored procedures, and of late, Dynamic Management Views, to gather system statistics, none were graphical. These new reports provide a quick easy way to print off a professional looking report for presentations or discussions. This article will examine the built in reports called SQL Server Management Studio Reports and an additional add-on called Performance Dashboard. Prerequisites

The Management Studio Reports are SSRS (Server Server Reporting Service) generated, but SSRS is not required to be installed. SQL Server Service Pack 2 is not required for the built in reports on SQL Server 2005, but is required for the optional add-on Performance Dashboard. Once Service Pack 2 is installed however, custom reports can be created and run like a built in report. This article will assume Service Pack 2 is installed. The service pack can be downloaded from Microsoft’s web site at the following URL:

Running Reports
To run a report, right click a database and select Reports:

Then select Stand Reports. A list of seventeen built in reports will be displayed. These include:
* Disk Usage
* Disk Usage by Top Tables
* Disk Usage by Table
* Disk Usage by Partition
* Backup and Restore Events
* All Transactions
* All Blocking Transactions
* Top Transactions by Age
* Top Transactions by Blocked Transactions Count
* Top Transactions by Locks Count
* Resource Locking Statistics by Objects
* Object Execute Statistics
* Database Consistency history
* Index Usage Statistics
* Index Physical Statistics
* Schema Change history
* User Statistics

Let’s take a look at a report; run the Disk Usage report displayed below:

This report provides a quick professional looking graphical view of disk usage. As a side note, reports like these can be generated for a web page in Visual Studio also without SSRS being installed. Use the ReportViewer control in “local mode”.

The Disk Usage by Top Tales provides a quick report of table record counts and disk usage as shown below.

Source Code
SQL Server Profiler can be used to capture the underlying TSQL used by these graphical reports. Often the code is complex but can provide some interesting insight into the Dynamic Management views and procedures used. To start Profiler on SQL 2005, go to Start, Programs, Microsoft SQL Server 2005, Performance Tools, and then select SQL Server Profiler. Once opened, select File, New Trace, connect to your server, and then select “TSQL” from the “Use the template” drop down list. Click Run. Now return to the SQL Server Management Console and run a report. Profiler will populate with the TSQL executed as shown below.

Performance Dashboard
In addition to the standard built in reports, custom reports can also be run from the SQL Server Management Studio. A free family of custom reports available from Microsoft is the Performance Dashboard for SQL Server 2005. The Dashboard is a set of reports used to monitor performance and help diagnose performance problems. For example, the Performance Dashboard can help resolve CPU bottlenecks, IO bottlenecks, and Blocking problems.

Once the Dashboard is downloaded, run the MSI to begin installation. Note the file installation path, such as C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard . Once complete, run the “setup.sql” file inside the SQL Server Management Studio. This setup file needs to be run on each SQL Server you wish to monitor using the Dashboard. In the same directory is a help file called PerfDash.chm. Inside the help file is a section worth reading called Troubleshooting Methodology.

To run the dashboard once installation is complete, right click a database and select Reports, Custom Reports. Browse to your installation folder then select performance dashboard main.rdl. The other reports in the folder are called from this main.rdl (Report Definition Language). If you try to run one directly, like traces.rdl, some type of error will be thrown. The main.rdl is shown below. From this screen, you can drill into IO, Waits and session information.

Custom Reports
The Performance Dashboard is an example of a custom report. In addition to the custom reports available from Microsoft, you can create your own custom reports by using BIDS (Business Intelligence Development Studio). BIDS is included with SQL Server. To create a custom report, we’ll generate a RDL (report definition language) file from BIDS, and then execute it from the SQL Server Management Studio.

To begin, select Start, Programs, Microsoft SQL Server 2005, and then click SQL Server Business Intelligence Development Studio. From the top menu, select File, New, and then Project. The Project Type should be “Business Intelligence Projects” and the template should be Report Server Project Wizard. Enter a name and location and then click OK. Click “Next” on the splash screen. Create a new data source as shown below by clicking the Edit button and specifying your server name and database. This example will use Adventure Works.

Full article...