SQL Server Management Studio Reports and Dashboard

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: 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.

»


See All Articles by Columnist
Don Schlichting

Don Schlichting
Don Schlichting
Don Schlichting is a Microsoft MSCE, MCDBA and an Oracle Certified Associate. Working at Professional Control Corporation as the IT Director,.

Latest Articles