SQL Server – Disadvantages of Disabling Lock Escalation

sql server

Using SQL Sever 2016 at AWS on I3 32 cpu 240GB 2012R2 server.

We have import process that inserts as select 100,000s or rows into table A. Users when they log in need to update table A.

Import process is is causing lock escalation since it uses more than 5000 locks. This blocks users from logging in.

We can alter the table to disable lock escalation.

However, what is the cost of doing so.

Is it just that we consume more memory for locks ( we have enough at 200GB) and that is it? Or are the any other problems.

Best Answer

As Erik mentioned and most experts including Microsoft books online online suggest reducing batch size/change isolation level/rewrite query but not disabling lock escalation.

Ref:

Lock Escalations are an optimization technique used by SQL Server to control the amount of locks that are held within the Lock Manager of SQL Server.

Lock escalation serves a very useful purpose in SQL Server by maximizing the efficiency of queries that are otherwise slowed down by the overhead of acquiring and releasing several thousands of locks. Lock escalation also helps to minimize the required memory to keep track of locks. The memory that SQL Server can dynamically allocate for lock structures is finite, so if you disable lock escalation and the lock memory grows large enough, attempts to allocate additional locks for any query may fail and the following error occurs:

Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

Escalation Threshold for an Instance of the Database Engine

Whenever the number of locks is greater than the memory threshold for lock escalation, the Database Engine triggers lock escalation. The memory threshold depends on the setting of the locks configuration option:

  • If the locks option is set to its default setting of 0, then the lock escalation threshold is reached when the memory used by lock objects is 24 percent of the memory used by the Database Engine, excluding AWE memory. The data structure used to represent a lock is approximately 100 bytes long. This threshold is dynamic because the Database Engine dynamically acquires and frees memory to adjust for varying workloads.
  • If the locks option is a value other than 0, then the lock escalation threshold is 40 percent (or less if there is a memory pressure) of the value of the locks option.

Similar question was asked before, answer was not accepted but has some useful information.

drawbacks of disabling lock escalation