Although row-level locking is great from a concurrency standpoint, it is expensive. In memory, a lock structure uses 64 bytes in 32-bit and 128 bytes in 64-bit operating systems. Keeping information about millions of row- and page-level locks would require SQL Server to allocate gigabytes of RAM to storing them.
SQL Server reduces the number of locks held in memory with a technique called lock escalation, which we will discuss in this chapter.
Lock Escalation Overview
Once a statement acquires at least 5,000 row- and page-level locks on the same object, SQL Server tries to escalate, or ...
