Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 378

from master..sysprocesses
where program_name = 'SQLCMD'
waitfor delay '00:00:00.05'
end

spid kpid blocked waittime lastwaittype

52 4212 0 546 LOGMGR_RESERVE_APPEND

52 4212 0 0 SOS_SCHEDULER_YIELD

There was a pretty even split between these two wait types. SQL Server Books Online (SQL BOL) explains
what each of the wait types mean. Look these up either by searching for the wait type or by searching for
sys.dm_os_wait_stats .

The LOGMGR_RESERVE_APPEND occurs when you are waiting to see if truncating the log will give you
enough space to write the current log record. In this case, the database was configured with the simple
recovery model, and the log file is on a very slow disk, so you should expect to see a lot of log-related
waits.

SOS_SCHEDULER_YIELD occurs when a task voluntarily yields the scheduler and has to wait for a new
quantum. These are quite different from the wait types in the insert query, which is expected because the
update has a very different characteristic than the insert.

Figure 11-5