Professional SQL Server® 2005 Performance Tuning

9.9. Tuning for Deadlocking and Blocking

Chapter 4 covers the topics of blocking and deadlocking, including techniques for capturing and recognizing blocks and deadlocks are described. This section discusses techniques and practices for handling these events.

9.9.1. Blocking

Before proceeding a brief review of blocking is needed. Blocking isn't necessarily a problem. It occurs regularly during the normal operation of a database. A typical scenario for blocking occurs when one user who's updating a data page prevents another user from accessing that page at the same time. When this happens the second user is blocked for a few moments until the first user completes the change and releases the page. This isn't the issue that causes performance degradation. The performance-robbing blocks are the ones that last for more than a few moments — a lot more. Imagine standing in line at a grocery store checkout. The line is moving well. The customers are steadily progressing through the line when the cashier has to stop the process and get a price check on an item. At this point everyone in the line is stopped or blocked until the price check is completed. This is the type of blocking that needs to be minimized.

In order to aid your investigation of blocking, SQL Server has provided some DMVs that are useful. Specifically, the sys.dm_os_wait_stats and sys.dm_os_waiting_tasks are particularly handy. See Chapter 4 for a more thorough description of these DMVs as well as other blocking tools. ...