How To Check Lock Escalation On A Table

When you are a DBA and your colleagues start talking about lock escalation it’s a good idea to understand exactly what it is, the effect it can have and how you can disable it if necesary.

Lock Escalation In SQL Server

Lock escalation is essentially when SQL takes small locks like row or page locks and escalates them to a larger lock, e.g a page lock.

Locks are directly escalated to table locks (except for partitioned tables), so even if it is a row lock, it will not get escalated to a page lock first.

Partitioned tables may not escalate directly to a table lock – they may escalate to the HoBT level instead.

How To Check Lock Escalation

You can run this query

SELECT t.name, lock_escalation_desc
FROM sys.tables t

Another way I have found to identify lock escalation is by using a trace. You can choose the lock escalation event within a trace and observe when they are occuring.

How To Disable Lock Escalation

Disabling lock escalation is relatively simple at the table level.

Example

---------------------------------------
ALTER TABLE SampleTable SET (LOCK_ESCALATION = DISABLE)
---------------------------------------

Thats all it takes, be careful though sometimes doing what seems the right thing to do, can actually be the wrong thing to do.

SQL is pretty good at taking care of it’s locking and blocking without your help.

Why is the lock escalating, can you refine the query? Maybe its a good thing, you may get unexpected results if you suddenly stop this happening.

As always test, test and test some more.

When it all turns to crap and the finger is pointed at you, it pays to have done everything you could have concievably done to prove that the path you decided to take was the right one.

Document your tests, and make sure you point out (in documented statements) any assumptions you might be making.

Better Ways To Stop Lock Escalation

There are a couple of other and preferred methods to avoid lock escaltion.

  • Make the query more efficient to stop the query crossing the lock threshold
  • Break the operation down into smaller batch sizes

If you would like to learn more about lock escalation you will find great value in the book SQL Server internals.

Rob StGeorge

Senior SQL Server Database Administrator residing in Auckland, NZ

3 Comments

Leave a Reply