SQL Server 2005 Performance Reports

In their never-ending quest to make the life of a SQL DBA easier (please, don't laugh, it's really true!), Microsoft has recently released a series of custom reports that plug into SQL Server Management Studio (SSMS) that help to track down performance-related issues with SQL Server 2005 (You can download and read about them here). I recently had the opportunity to work with these reports first-hand in a production environment, and while I am normally very skeptical of such things, I have to admit that this toolset really impressed me.

Installing the Performance Reports

The installation of the Performance Reports package is relatively straightforward, although you do have to perform a couple of steps:

1. Download the SQLServer2005_PerformanceDashboard.msi file and execute it. This will unpack a bunch of report RDL files and sql scripts into your SQL Server tools folder (default is C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard).
   
2. Execute the setup.sql file on each server instance that you plan to monitor with these reports. (Note: You do not need the RDL files on each server)
   

Using the Performance Reports

Once the reports are loaded, you access them by right-clicking on a server in the object explorer inside SSMS and selecting Reports/Custom Reports. Then browse to the installation folder (Note: You may not see any files when you browse in management studio due to the fact that it might be looking for *.rdlc files instead of *.rdl – To solve this, simply type *.rdl in the filename box) and select "Performance_Dashboard_Main.rdl". This will open the main dashboard as shown below:

Each of the items in the dashboard is capable of drilling down into more detail. For example, in my case, when I selected the "Current Waiting Requests" graph, I was presented with the following:

Notice that the report shows the specific query that is causing the wait in this case. Clicking on the query brings up the execution plan as follows:

Note that the report shows that there are identified missing indexes that could help the performance of the query. Clicking on the "View Details" link brings up the following report:

Which can now be used to create the missing index that should help the performance of that particular query. (The reality is you want to perform a more complete index analysis using the tuning wizard and a captured workload, but this is a good start!)

Now, I know not everyone is the SQL Geek that I am, but come on, this stuff is COOL!