Disable Lock Escalation in SQL Server

by Pythian Marketing on Apr 10, 2015 12:00:00 AM

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.

But that was in most cases not a good choice and caused a lot of performance problems. In SQL-Server 2008 and above there is a new table option (ALTER-TABLE) that can be used to change the default Lock-Escalation. This helps you if you have a table where you want to disable the escalation or if the table is partitioned. On a partitioned table activating the AUTO Option can improve concurrency, by escalating the locks to the partition-level and not to the table-level. ALTER TABLE - table option: SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

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

This is a cool feature, that are many developers are not aware of. Thanks for Reading!

Topics: Google Cloud Platform Google Cloud Big Data Technical Track Cloud BigQuery Advanced Analytics Solutions Architecture Data Analytics Platform

No Comments Yet

Let us know what you think

See all

Share this

Disable Lock Escalation in SQL Server

Share this

No Comments Yet

Subscribe by email