SQL Server Lock Escalation and handling it at partition level

SQL Server locking mechanism makes sure that database is consistence during a transaction and it makes the database available for as much as concurrent users while a transaction is being run. Locks are acquired at both reading and writing. Locks acquired for reading, with default Isolation Level, are released as soon as the SELECT is completed but locks acquired for writing are held until the transaction is completed.

Lock Escalation

Locks are acquired at an appropriate level. It can be at key level, page level or entire table. The cost for maintaining locks is high when many locks are acquired for a transaction, hence SQL Server tries its best for minimizing the number of locks need to hold. It is done by converting fine-grained locks held by a transaction on a single resource to a single coarser-grained lock on the same resource. This is called Lock Escalation.

The Lock Escalation happens from either row or page locks to a table lock. It never happens from row locks to a page lock. With previous releases of SQL Server, the condition for deciding to escalate locks was hard-coded based on certain number of records that are locked at either row or page level. However, SQL Server 2016 uses multiple factors and one of them is memory consumption. In most cases, when lock manager consumes more than 40% of allocated memory, Lock Escalation can happen.

ALTER TABLE SET ESCALATION LEVEL

Whether the table is partition or not, the default behavior is to escalate to table level. The ALTER TABLE SET ESCALATION LEVEL that is used for controlling this, accepts three values;

1. TABLE: The default value. This escalates to table level whether the table is partioned or not.
2. AUTO: This is same as above but it escalates to partition level if the table is partitioned.
3. DISABLE: This prevents lock escalation but it does not guarantee.

Disabling at session level

If you need to stop escalation at session level, use can enable Trace Flag 1224 for the session. This stop Lock Escalation but it still can occur due to the memory pressure.

However, if you need to completely stop it, you can use 1211 instead of 1224. You need to be cautious on this as it might degrade the performance.

The following code shows how SQL Server escalates locks with different settings. The table InternetSales_Partition is partitioned by its OrderDateKey and default settings have not been changed.

BEGIN TRAN
 UPDATE dbo.InternetSales_NonPartition
  SET TaxAmt = TaxAmt * 10
 WHERE OrderDateKey BETWEEN 20130501 AND 20130831

--ROLLBACK TRAN

If we check the locks acquired using sp_lock;

As you see, entire table is locked with its default settings. Let's set it with AUTO option and see how it works.

ALTER TABLE dbo.InternetSales_Partition
SET (LOCK_ESCALATION = AUTO)

BEGIN TRAN
 UPDATE dbo.InternetSales_Partition
  SET TaxAmt = TaxAmt * 10
 WHERE OrderDateKey BETWEEN 20130501 AND 20130831

--ROLLBACK TRAN

As you see, now it is at the partition level, not at the table level. This increases the concurrency even though the Lock Escalation has occurred.