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

Running Scheduler 1 Suspended (waiting)

SPID 55 Running SPID 52 PAGEIOLATCH_SH

SPID 54 CXPACKET
SPID 60 LCK_M_S

Runnable Scheduler 1

SPID 53 Runnable
SPID 52 Runnable
SPID 59 Runnable
SPID 61 Runnable

Figure 4-1

All this talk of waiting makes it sound like a horrendous thing to find on your system. In fact it's perfectly
normal and expected to see waits. This is how SQL Server scales so efficiently. You're really looking for
unexpected waits and large wait times when troubleshooting performance.

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).

❑