SQL Server: LOCK ESCALATION

By default, SQL Server will escalate locks to the table level when required. When an excessive number of row-level locks are encountered, SQL Server will escalate the lock to a higher level. This causes the entire table to be locked, which is usually sufficient and reduces memory consumption. However, with partitioned tables, DBA/Developer may not want to lock the entire table, but only a single partition, such as the partition containing rows being modified. This would allow queries against other partitions in the table to execute successfully without being blocked. DBA/Developer can use the LOCK_ESCALATION setting for a table to control how SQL Server will escalate locks. The LOCK_ESCALATION setting can have one of the following values:
TABLE: Lock escalation is performed to the table level, which is the default.
AUTO: Lock escalation is performed to the table level for non-partitioned tables, and to the partition level for partitioned tables.
DISABLE: Lock escalation is disabled, and lock escalation does not typically occur. SQL Server only escalates locks in specific situations where it is absolutely required to ensure data integrity.
For example, the following statement would set the LOCK_ESCALATION setting for the POHistory table to AUTO:

ALTER TABLE POHistory

SET (LOCK_ESCALATION=AUTO);

With this setting, SQL Server will escalate locks to the table level, rather than the partition level.