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:
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:
Similar question was asked before, answer was not accepted but has some useful information.
drawbacks of disabling lock escalation