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.
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
- 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