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

CommonUsageScenarios
Now that you know how to open a custom report and you have the basic idea of how the Performance
Dashboard Reports work, look at the first report: the Performance Dashboard.
PerformanceDashboard
This report is designed to give a quick overview of the performance of a SQL Server 2005 instance. There
are two main graphs: System CPU Utilization and Current Waiting Requests.
The System CPU Utilization graph shows the last 15 minutes of CPU usage. It breaks down CPU usage
by the SQL Server instance processor usage compared to other processes. The data in this graph is not
obtained through performance counters. Instead, it is obtained through the DMV sys.dm_os_ring_buffers.
If you would like to see how this is calculated without using performance counters, run the following:
USE [MSDB]
GO
sp_helptext 'MS_PerfDashboard.usp_Main_GetCPUHistory'
GO
The Current Waiting Requests graph in the upper right hand corner groups similar waits together in
predefined wait categories such as Lock, Network IO, Buffer IO, and Sleep. If you would like to see how
these are grouped, run the following:
USE [MSDB]
GO
sp_helptext 'MS_PerfDashboard.fn_WaitTypeCategory'
GO
sp_helptext 'MS_PerfDashboard.usp_Main_GetRequestWaits'
GO
An alert with a yellow triangle and black exclamation point will be displayed above each of these graphs
if certain common bottlenecking conditions are met. For example, if the CPU Utilization is greater than
80 percent, it is likely that your instance is CPU bound, and you will be given recommended next steps
based on whether the CPU is used by SQL or another process.
Locking/Blocking
The Performance Dashboard makes it easier to identify resource waits. On the Current Waiting
Requests graph in the upper right hand corner of the Performance Dashboard, you can see both the
total length of process wait time as well as the total number of processes waiting on a specific wait type
(see Figure 13-16).
You can see in this example that there are six waiting sessions: Five are waiting on Lock and one is
waiting on Sleep. The total wait time for Sleep is almost 100 seconds and the total wait time for Lock
is over 200 seconds. If you click the blue bar representing Lock, you will be prompted to open another
report tailored to blocked sessions (see Figure 13-17).
Choose Run to bring up the Blocking Report (see Figure 13-18).