Troubleshooting Blocking
There are three general steps to investigate and deal with blocking:
- Identify that blocking occurred.
- Identify the cause of the blocking.
- Eliminate the cause of the blocking.
This chapter focuses mostly on the first two steps. Because blocking can occur in a variety of components, and the causes can be specific to those components, the solutions often are component-specific, too. Rather than include detailed information on these components in this chapter, I make references to the documentation for those components. However, I have included causes and solutions for some of the more common blocking types.
Note that many of the examples and the discussion assume SQL Server 2005 SP1 (or later). This is particularly the case for the sys.dm_os_waiting_tasks DMV. Several changes and fixes for this DMV were included in SP1. The SQL Server 2005 RTM behavior would have significant differences compared to what is described next.