Professional SQL Server® 2005 Performance Tuning

4.3. Common or Noteworthy Resource Wait Types

The following wait types are worthy of mention for their regularity in a system or because their meaning should be understood. For a description of all the wait types, search in SQL Server 2005 Books Online for sys.dm_os_wait_stats.

• WAITFOR: Is the resulting wait after issuing the WAITFOR T-SQL command. It is a manually instigated wait and shouldn't be considered a performance issue.

• LAZYWRITER_SLEEP: Signifies time that the lazywriter process is sleeping and waiting to run. It should not be considered in a performance profile.

• SQLTRACE_BUFFER_FLUSH: Occurs when the system is waiting for a SQL Trace buffer to be written to disk. You will see this on most servers in SQL Server 2005 because a rolling 100 MB trace runs permanently by default in the background and is used by the management reports in SQL Server Management Studio. You can normally discount this wait as an ever present feature.

• SLEEP_BPOOL_FLUSH: In SQL Server 2005 checkpoint operations are throttled to prevent them from overloading the disk subsystem and waiting for this operation is represented by SLEEP_BPOOL_FLUSH.

• RESOURCE_SEMAPHORE: All hash, sort, bulk copy, and index creation operations require space in what is called workspace memory, which is dynamically managed between 25 percent and 75 percent of non-AWE memory (see Chapter 16 for a description of AWE).

  This wait represents time spent pending a memory grant in workspace memory and should be correlated with the Memory ...