Note: This blog post is from 2007. Some content may be outdated--though not necessarily. Same with links and subsequent comments from myself or others. Corrections are welcome, in the comments. And I may revise the content as necessary.Those using SQL Server 2005 may want to take note of a new "Dashboard Report" option for SP2, to help monitor and resolve performance problems, including capturing diagnostic info when a problem is detected.
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)
- Latch contention
The report is an extension of the Custom Reports feature introduced in the SQL Server 2005 SP2 release of SQL Server Management Studio. Note that Reporting Services does not need to be installed.
The reports retrieve info from dynamic management views. They don't poll performance counters or require tracing be enabled. They also do not store a history of performance over time. So it's a lightweight (yet powerful) monitoring option.
You can get the extension itself at:
There's also a complete article about how to install it from Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I , at Database Journal (and from which I obtained the image above).