Disable Lock Escalation in SQL Server
Tags:
Google Cloud Platform,
Big Data,
Technical Track,
Cloud,
Google Cloud Platform (Gcp),
Bigquery,
Advanced Analytics,
Solutions Architecture,
Data Analytics Platform
If a lot of rows or pages are locked, the SQL Server escalates to a table-level lock, to save resources. Each single lock takes approx. 100 bytes. So if you have many locks it takes a lot of resources to manage them. (There is a great blog about lock escalation, if you want some more info:
https://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/05/17/lock-escalation.aspx) Until SQL Server 2008, there was no way to change the lock escalation for a single table. You could deactivate the escalation for the server by using the Trace Flags:
- 1211 - Disables Lock Escalation completely - allows to use 60% of the allocated memory - if 60% of memory is used and more locking is needed you will get an out-of-memory error.
- 1224 - Disables Lock Escalation until the memory threshold of 40% allocated memory is reached - after that Lock Escalation is enabled.
- AUTO (should be considered if you have a partitioned table)
- If tables is partitioned - the locks will be escalated to the partition-level
- If table is not partitioned - the locks will be escalated to the table-level
- TABLE
- Default behavior
- Locks are escalated to the table-level
- DISABLE
- Lock escalation to the table-level is deactivated in most cases
- In some necessary cases it's allowed to escalate to the table-level
