Google Cloud Platform Google Cloud

Big Data Technical Track Cloud BigQuery Advanced Analytics Data Analytics Platform

Disable Lock Escalation in SQL Server

1 min read

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!

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Disable Lock Escalation in SQL Server

Share this

Share this

More resources

Analyzing Open.Canada.Ca datasets with Power BI

Magic Knobs in Replication Agents

Apache Cassandra 2.1 incremental repair

Ready to unlock value from your data?