Google Cloud Platform Big Data

Disable Lock Escalation in SQL Server

1 min read

Apr 10, 2015

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!

← PowerShell Script to Manipulate SQL Server Backup Files

My thoughts on the resilience of Cassandra →

Get Email Notifications

No Comments Yet

Let us know what you think

Disable Lock Escalation in SQL Server

Share this

You May Also Like

Comparing Pgpool-II and PgBouncer

Near Real-Time Data Processing for BigQuery: Part One

Near Real-Time Data Processing for BigQuery: Part Two

Get Email Notifications

No Comments Yet