Using Performance Dashboard to Increase Performance on SQL Server 2005

There is a script that runs on one of our servers that scrapes the output of a log page and emails the result to a set of admins.  Lately, however, the log databases have grown so large that some of the SQL queries run by this page were timing out.

Luckily, I found out about SQL Server Performance Dashboard Reports, a set of reports you can install to find out, among other things, where all the CPU is going, which queries are the most resource consuming, and how to add indexes to increase performance.

Installation

To get started, simply download the installer from the link above and install it on the machine running SQL Server.  My default installation directory looked like this:

C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard

Inside this directory are all the files needed to run the reports, a setup script, and a helpful CHM.  In addition to running the installer, a system administrator needs to run the setup.sql script located in this directory on every instance of SQL Server that you want profile.

Accessing Custom Reports

Then, to access the report in SQL Server Management Studio, right click on your server instance, select "Reports" then "Custom Reports" and open the performance_dashboard_main.rdl report in the installation directory.

Browse Expensive Queries and Create Missing Indexes

You can now browse the most expensive queries by CPU, Duration, Logical Reads/Writes, Physical Reads, or CLR Time.  Under "Miscellaneous Information", I found a link for "Missing Indexes".  This brought up a list of 4 indexes that it recommended that I create to improve database performance.  Suffice it to say that after creating the suggested indexes, CPU time was reduced an order of magnitude for the script.

For more information, I recommend this article:

http://www.sql-server-performance.com/articles/per/bm_performance_dashboard_2005_p2.aspx