Reducing blocks

Blocking occurs when one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks.

By default, a connection will wait indefinitely for the blocking lock to end.

Possible causes of excessive blocking include:

• long-running queries
• canceling queries without rollback
• changing large numbers of records in a single transaction
• lack of appropriate indexes

Links to more information

http://www.sql-server-performance.com/tips/blocking_p1.aspx
(SQL Server Performance: How to Minimize SQL Server Blocking)

http://www.mssqltips.com/tip.asp?tip=1359
(Locking and Blocking Scripts in SQL Server 2000 vs SQL Server 2005)

http://technet.microsoft.com/en-us/magazine/cc434694.aspx
(TechNet: Minimize Blocking in SQL Server)