What is disadvantage of disabling lock escalation in SQL Server 2016

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.

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:

• How to resolve blocking problems that are caused by lock escalation

in SQL Server

• Lock Escalation (Database Engine)

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

StackExchange Database Administrators Q#186491, answer score: 8