Troubleshooting performance issues in SQL Server involves studying the wait types and wait times associated with the queries executing. However, the relevance of each of the various wait types in the output can be a mystery.
For SQL Server 2000, the cumulative wait types can be observed using the DBCC SQLPERF(WAITSTATS) command. To clear the current wait times, use DBCC SQLPERF(WAITSTATS, CLEAR). The KB article The waittype and lastwaittype columns in the sysprocesses table in SQL Server 2000 explains the more common ones. A more comprehensive reference is found in Tom Davidson’s excellent article Opening Microsoft’s Performance-Tuning Toolbox. The article’s code download has an extremely useful table which details almost all the wait types and explains their correlation with the performance counters.
For SQL Server 2005, we have a good reference in SQL Books Online itself, as part of the sys.dm_os_wait_stats reference documentation. And if you haven’t read it yet, don’t waste a minute and read the excellent Troubleshooting Performance Problems in SQL Server 2005 white paper.