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: http://www.microsoft.com/downloads/details.aspx?familyid=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&displaylang=en . The built in reports are included with the install on SQL Server 2008.

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. It can be downloaded from the following Microsoft web site: http://www.microsoft.com/downloads/details.aspx?familyid=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en . Service Pack 2 for SQL Server 2005 is required prior to the Performance Dashboard being used.

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.

Click “Next” and enter “SELECT * FROM Person.Contact” as the query sting, then click “Next” again. Leave the default of Tabular and click Finish. Supply a report name and click “Finish” again. Close BIDS. From SQL Server Management Studio, right click Adventure Works and select Reports, Custom, then browse to your newly created report. If the display formatting of the report is not good, reopen the report in BIDS and adjust.

Conclusion

SQL Server 2005 and SQL Server 2008 offer reports for quick and easy monitoring and troubleshooting. In addition, user created “custom” reports can be saved and executed in SQL Server Management Studio. The free Performance Dashboard can be very helpful in bottleneck troubleshooting. Included with the Performance Dashboard is a help file, which explains which reports to use for diagnosing various problems.