SQL Server 2005 Performance Dashboard Reports - Professional SQL Server 2005 Performance Tuning - page 493

❑ They focus on a single instance.

❑ They cannot be copied and pasted.

TheyRequireServicePack2
The Performance Dashboard Reports require SQL Server 2005 Service Pack 2 or later. The service pack
must be applied to each instance you wish to monitor. With Service Pack 2 there were a few bug fixes to
a couple of key DMVs as well as the introduction of new T-SQL functionality.

Service Pack 2 must also be applied to the workstation from which you're using Management Studio.
The entire custom reporting infrastructure was added with SP2.

This requirement will become less of an issue over time.

TheyOnlyPresentaSnapshotofData
The reports will only show information from one moment in time. They do not automatically refresh like
System Monitor or the Activity Monitor.

Also, the reports do not store any history. This means that you must diagnose a problem as it's occurring.
This can be difficult for transient waits such as Buffer IO or locks that last for a few seconds or less.

TheyDoNotComparetoBaseline
If your instance has 74 open sessions and 45 requests and is experiencing 10 blocking sessions for
30 seconds each, do you know if this is normal behavior? This could be how your applications behave, or
this could be terrible. If you personally aren't familiar with what is expected, the Performance Dashboard
Reports become a little less useful.

In an ideal world, you will capture a number of metrics over similar time intervals or during similar
workloads to create what is known as a baseline. You can then compare an interval or workload to
the acceptable baseline and determine whether something is out of ordinary. Unfortunately, you aren't
always working in an ideal world, and Performance Dashboard Reports don't provide the built-in
functionality to create a baseline for you.

Another tool was recently released from Microsoft: DMVStats ( www.codeplex.com/sqldmvstats ). It
complements the Performance Dashboard by providing a basic snapshot and baseline mechanism. It is a
shared source project available on CodePlex.

TheyAreInstance-Centric
The Performance Dashboard Reports can only show data for one instance at a time. This is an
improvement over looking at raw data from T-SQL queries on an instance-by-instance basis. However,
the reports are not a tool to monitor an enterprise. They do not have automated data gathering sessions
or a centralized reporting infrastructure like System Center Operations Manager.

Many SQL Server customers who have multiple instances of SQL Server will not have the time to run the
Performance Dashboard Reports on each instance. Those customers may resort to using the reports only
when SCOM alerts them that a performance problem may be happening on one of their instances.