7

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.

Improve this question
0

1 Answer 1

Reset to default
8

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

Improve this answer
4
  • Here is our problem. rewriting or reducing batch size will require dev work and testing time, at least 3-4 weeks at best (is big piece of code that does it in one single operation. We can hold off on imports for 3-4 days but that is it, and users need to log in now.
    – tony
    Commented Sep 21, 2017 at 11:34
  • I understand, you can definitely try and rollback if you reach the threshold. Hopefully your load has a marker that you can restart from.
    – SqlWorldWide
    Commented Sep 21, 2017 at 11:41
  • Also change isolation level helps with selects but not updates correct? In the article you linked, they mentioned doing this BEGIN TRAN SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0 WAITFOR DELAY '1:00:00' COMMIT TRAN As a temporary solution till it is rewritten is this a valid option? Any concerns?
    – tony
    Commented Sep 21, 2017 at 11:42
  • Yes and no. It can effect your writes too. In your case you mentioned users are being blocked from logging in, I hope you meant from accessing the object and not server. You might look at this article: technet.microsoft.com/en-us/library/ms188277(v=sql.105).aspx
    – SqlWorldWide
    Commented Sep 21, 2017 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.