Database Reference
In-Depth Information
In some cases, you may consider partitioning the tables and set lock escalation option to use partition-level
escalation, rather than table level, using the
ALTER TABLE SET (LOCK_ESCALATION=AUTO)
statement. This could help
in scenarios in which you must purge old data using the
DELETE
statement or run reporting queries against old data in
REPEATABLE READ or SERIALIZABLE isolation levels. In those cases, statements escalate the locks to partitions, rather
than tables and queries that are not accessing those partitions, would not be blocked.
In some cases, you can switch to optimistic isolation levels, which is discussed in Chapter 21. Finally, you would
not have any reader-related blocking issues in the READ UNCOMMITTED transaction isolation level where shared (S)
locks are not acquired, although this method is not recommended because of all the other data consistency issues it
introduces.
Summary
SQL Server escalates locks to the object level after the statement acquires and holds about 5,000 row- and
page-level locks. When escalation succeeds, SQL Server keeps the single object-level lock, blocking other sessions
with incompatible lock types from accessing the table. If escalation fails, SQL Server repeats escalation attempts after
about every 1,250 new locks are acquired.
Lock escalation fits perfectly into the
“it depends”
category. It reduces the SQL Server Lock Manager memory
usage and overhead of maintaining the large number of locks. At the same time, it could increase blocking in the
system because of the object- or partition-level locks held.
You should keep lock escalation enabled, unless you find that it introduces noticeable blocking issues in
the system. Even in those cases, however, you should perform root-cause analysis as to why blocking due to lock
escalation occurs and evaluate the pros and cons of disabling it. You should also look at the other options available,
such as code and database schema refactoring, query tuning, as well as switching to optimistic transaction isolation
levels. Either option might be the better choice to solve blocking problems, rather than disabling lock escalation.
