SQL Server 2005 Performance Dashboard Reports

The SQL Server 2005 Performance Dashboard reports were recently released as a free download for customers. The performance dashboard was developed as a joint effort between the support and development teams with a goal of reducing the amount of time spent discovering the source of a performance problem and allowing efforts to be focused on resolving the problem. The dashboard reports rely exclusively on the SQL Server 2005 dynamic management views, utilizing data that is already captured and always available. Consequently there is zero performance impact of using the dashboard except when you actually open/refresh a report. Hopefully the dashboard will also allow customers to find and resolve many of the problems where they previously may have needed to call for support.

The main dashboard page allows a database administrator to quickly identify whether there is a current bottleneck on their system (CPU or waits). If a bottleneck is present, you can click on the charts to drill through and capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.

Common performance problems that the dashboard reports may help to resolve include:
- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer (missing indexes)
- Blocking
- Latch contention

The troubleshooting methodology of identifying your bottleneck and then finding the queries using that resource is consistent with the approach historically used by support. However, the DMVs and the dashboard may allow you to do a significant portion of the troubleshooting live (as opposed to capturing trace and other data and reviewing the data after the fact). If you call SQL Server support for assistance with a performance problem and are running SP2 your support engineer may request that you install the reports to speed the troubleshooting process.

For more information on the available reports and how to use them please see the help file, perfdash.chm.

Keith Elmore

Comments

• Anonymous
  July 12, 2007
  Tom Davidson wrote: I've been working with database applications for the last 25 years. A particular

• Anonymous
  July 19, 2007
  The missing index DMVs are a really cool new feature in SQL Server 2005 that, in my opinion, have been

• Anonymous
  July 19, 2007
  The missing index DMVs are a really cool new feature in SQL Server 2005 that, in my opinion, have been

• Anonymous
  March 29, 2008
  I've got a series on the standard reports in SQL Server Management Studio, linked here: http://blogs.msdn.com/buckwoody/archive/tags/Standard+Reports/default.aspx