SQL Server Wait Types - Professional SQL Server 2005 Performance Tuning - page 72

It will clear out data that we're not interested in and give you a fairly clean measurement:

signalWaitTimeMs %signal waits resourceWaitTimeMs %resource waits
---------------- ------------- ------------------ ---------------
445837 17.92 2042154 82.08

Signal waits should be tiny in comparison to resource waits, so these results indicate a very heavy
demand for the CPU.

DMVStats
DMVStats is a great new performance tuning tool from the SQL Server Customer Advisory Team at
Microsoft that enables you to set up an automated performance data warehouse for a SQL Server 2005
instance. It installs a number of data gathering scripts which are scheduled to pull data out of your
instance and insert it into a database that can then be used to report on past performance. A number
of SQL Server Reporting Services reports are included for you to view the data and even configure the
data collection. It's surprisingly straightforward to set up and use and includes a couple of good reports
focused on waits. You can see the list of available reports in Figure 4-2. Figure 4-3 shows the Wait Stats
report, which groups all the wait types into a chart with the ability to drill-through. Figure 4-4 shows
the Executing Requests and Waiting Tasks report, where I have drilled down into I/O waits to see the
statement within a stored procedure that had a wait on WRITELOG.

Figure 4-2