SQL Server Control Lock escalation

Background knowledge:

Lock escalation Roadmap, row-to-page (extent)-to-zone (table partitioning)-and table;

ALTER TABLE controls the escalation behavior of the lock:

1. The default behavior in table SQL Server 2008, when set to this value, enables lock escalation at the table level, regardless of whether the table is partitioned.

2, Auto If you enable lock escalation at the partition level as a partition, enable lock escalation at the table level if there is no partition.

3, disable can not upgrade the lock to the table level, but for the use of TABLOCK hint or use serializable isolation level under the heap query we can still see the existence of table locks.

Setup method:

ALTER TABLE dbo. Nums
Set
(lock_escalation= Auto);
Go

Summarize:

I think this can differentiate the concurrency of the table.

SQL Server Control Lock escalation