SQL Server 2005 Waiting and Blocking Issues
This chapter is from the book
This chapter is from the book
This chapter is from the book
Santeri Voutilainen
In This Chapter
- Wait Types
- Troubleshooting Blocking
- Identifying Blocking
- Identifying the Cause of Blocking
- Resource Type Specifics
- Deadlocks
- Monitoring Blocking
- Conclusion
- Other Resources
I will start this chapter on blocking by talking about waiting. Why would I start a chapter on blocking with waiting? Well, the two are very much related, and they are often treated as synonyms. Because they are so related, the SQL Server concepts and tools related to each are intermingled; therefore, it is important to distinguish one from the other.
Conceptually, waiting usually refers to an idle state in which a task is waiting for something to occur before continuing execution. This “something” might be the acquisition of a synchronization resource, the arrival of a new command batch, or some other event. Although this description is generally accurate, there is an important caveat: Not all tasks identified as waiting within SQL Server are in fact idle. This is because the waiting classification is sometimes used to indicate that the task is executing a particular type of code. This code is often outside the direct control of SQL Server. In effect, the SQL Server task is waiting for the completion of the external code.
Wait Types
All waits within SQL Server are categorized into wait types. The current wait type for a task is set based on the reason for the wait. Each wait type is given a name that describes, to some extent, the location, component, resource, or reason for the wait.
Although some of the names can be somewhat cryptic (I cover some of these later), others are self-explanatory. The list of wait types is available from the sys.dm_os_wait_stats dynamic management view (DMV). By default, the output from this DMV is not the full list. For SQL Server 2005, the SQL Server product team opted not to include some wait types that fall under one of the following three categories:
- Wait types that are never used in SQL Server 2005; note that some wait types not excluded are also never used.
- Wait types that can occur only at times when they do not affect user activity, such as during initial server startup and shutdown, and are not visible to users.
- Wait types that are innocuous but have caused concern among users because of their high occurrence or duration.
Unfortunately, the omission of these wait types can also lead to concern because the last category of excluded wait types does appear in other sources of waits—namely, the sys.dm_os_waiting_tasks DMV. The complete list of wait types is available by enabling trace flag 8001. The only effect of this trace flag is to force sys.dm_os_wait_stats to display all wait types.
dbcc traceon (8001, -1)
The list of wait types can be divided into four basic categories: Resource, Timer/Queue, IO, and External. The Resource waits category is by far the largest. It covers waits for resources such as synchronization objects, events, and CPU. The Timer/Queue wait category includes waits where the task is waiting for the expiration of a timer before proceeding or when a task is waiting for new items in a queue to process. The IO category contains most wait types related to IO operations. Both network and disk IO are included. The External waits category covers the cases mentioned earlier, where the task is executing certain types of code, often external to SQL Server, such as extended stored procedures.
The list of tasks currently in the waiting state is available from sys.dm_os_waiting_tasks. With regard to waiting, this DMV includes information identifying the waiting tasks, the duration of the wait, the wait type, and, in some cases, additional information about what is being waited for. The DMV also includes blocking-specific information—namely, the identity of the process blocking the continued execution of the task, when the identity is known. It is also the root source of blocking information, so knowing how to distinguish blocking from plain waiting using this DMV is important.
Blocking is distinguished from waiting in that the wait is not voluntary; instead, it is forced on the waiting worker by another task preventing worker’s task from proceeding. This occurs when the tasks attempt simultaneous access of a shared resource.1 This definition is generally considered to exclude the wait types in the External and Timer/Queue categories from indicating that the waiting task is blocking, even though the definition does not strictly exclude the External category. Nevertheless, short sections on both External and Timer/Queue waits are included toward the end of the chapter.