Lock Escalation for Sql Server

Lock escalation is the process of converting many fine-grained locks (such as row or page locks) into table locks.

When many locks created on the same table’s row in Same session the Sql Server itself automatically upgrades these locks into a table lock, it is count all locks like page, row and index-level locks on the a single table.

Escalation’s threshold values is 5000 locks (on single table) it’s maintained by Sql Server itself and we no need to change in configuration.  

Note: Escalation cannot occur if a different SPID is currently holding an incompatible table lock.

Lock Escalation Modes:–

1. Table: Table is the default mode and specifies that lock escalation will be done at table-level granularity.
2. Auto: Auto mode allows the Database Engine to select the lock escalation granularity that is appropriate for the table schema.
3. Disable:  allows to disable lock escalation.

How to Check Escalation Modes:–

We can check any specific table’s escalation mode using sys.tables like

select name,object_id,lock_escalation_desc  from sys.tables where name =’saleshead’

Change Escalation Mode: We also can change it using ALTER TABLE statement like

How to check/ trace Escalation: many ways to can trace it now try will profiler

1. login in profiler and select tsql_locks or also you can use blank template like :

2. Now Select Lock Escalation Events form “Events Selection” Tab there events lists come according to your selection template In that case I have selected Blank template

3. Finally check where and which query

How to Prevent Locks:

1. To keep short transactions: Break up large batch operations into several smaller operations. For example, suppose we deleting several hundred thousand old records then we found that it caused a lock escalation that blocked other users.

DELETE FROM sqllogs WHERE datastamp < ‘20190101’

Break above query like that:

SET ROWCOUNT 500

delete_more:

     DELETE FROM sqllogs WHERE datastamp< ‘20190101’

  IF @@ROWCOUNT > 0

           GOTO delete_more

SET ROWCOUNT 0

2. Reduce lock footprint of expensive queries by doing performance tuning and making them efficient.