This article discusses how to determine whether lock escalation is causing blocking and how to deal with undesirable lock escalation. However, some application or query designs might trigger lock escalation at a time when this action not desirable, and the escalated table lock might block other users. Typically, SQL Server's default behavior causes lock escalation to occur only at those times when it would improve performance or when you must reduce excessive system lock memory to a more reasonable level. When it makes this decision, SQL Server considers the number of locks that are held on a particular scan, the number of locks that are held by the whole transaction, and the memory that's used for locks in the system as a whole.
Microsoft SQL Server dynamically determines when to do lock escalation. Lock escalation is the process of converting many fine-grained locks (such as row or page locks) to table locks.
