Partitioning and LOCK_ESCALATION = AUTO

Partitioning is, among other things, a great feature when it comes to storing new data in the table as quickly as possible. The locking behavior for the table in particular is a decisive advantage of concurrency when using LOCK_ESCALATION = AUTO. There are a few things – e.g. non-aligned indexes – to consider that prevent this advantage from coming into effect.

Advantage of Partitioning

Table partitioning in Microsoft SQL Server divide large tables into smaller, more manageable partitions based on a key column. This enhances data access efficiency, as queries can target only the relevant partitions instead of scanning the entire table. It simplifies maintenance tasks like data archiving or purging, as partitions can be swapped or truncated individually. Partitioning also supports better parallelism, enabling SQL Server to process queries across multiple partitions simultaneously. Additionally, it improves index management by enabling partition-specific indexing strategies, reducing overhead for large datasets. The last - but for this case - most important advantage of partitioning is the locking model with partitioned tables.

Default Locking Model in Microsoft SQL Server

In Microsoft SQL Server, the default locking model is hierarchical, escalating from finer to coarser granularity as needed to balance concurrency and resource usage. Using an exclusive lock (X-Lock) as an example:

1. Row Level locking is the most granular lock on a single row in a table. SQL Server acquires an exclusive lock on that row, preventing other transactions from reading or modifying it.
2. Page Level locking will be used with an Intent Exclusive Lock to allow other transactions reading other rows within the page but not the row with the X-Lock.
3. Table Level locking acts the same way as Page Level Locking. It allows other transactions to read/write other parts of the table which do not have an X-Lock set.

This hierarchical escalation ensures efficient resource usage. The locking model is designed to minimize contention while ensuring data consistency and integrity.

Lock Escalation

Lock escalation in Microsoft SQL Server is the process of converting multiple fine-grained locks, such as row or - sometimes - page locks, into a coarser-grained lock, such as a table lock. This occurs when the number of locks held by a transaction exceeds a predefined threshold, which is set to conserve memory and reduce the overhead of managing numerous locks. Each single lock consumes 96 Bytes and when there are too much locks inside a transaction on ONE object, Microsoft SQL Server escalates the multiple locks into a TABLE-Lock.

Note: There has never been and will not be a hierachical locking from row to page to table. Whenever lock escalation jumps in it - by default - automatically locks the object (table) itself.

More ínformation about when Lock Escalation will happen can be found in the official Microsoft documentation here:

Lock Escalation in partitioned tables

The default behavior for partitioned tables is the same as for tables that are not partitioned. In fact, it would be fair to say that basically ALL tables in Microsoft SQL Server are partitioned. By default, each table has ONE partition.

	;WITH source
	AS
	(
		SELECT	TOP (50000)
				o_orderdate									AS	o_orderdate,
				ROW_NUMBER() OVER (ORDER BY o_orderdate)	AS o_orderkey,
				o_custkey,
				o_orderpriority,
				o_shippriority,
				o_clerk,
				o_orderstatus,
				o_totalprice,
				o_comment
		FROM	dbo.UploadData
		WHERE	o_orderdate >= '2025-01-01'
				AND o_orderdate <= '2025-01-31'
	)
	INSERT INTO dbo.orders
	(o_orderdate, o_orderkey, o_custkey, o_orderpriority, o_shippriority, o_clerk, o_orderstatus, o_totalprice, o_comment)
	SELECT	source.o_orderdate,
			last_number.max_o_orderkey + source.o_orderkey,
			source.o_custkey,
			source.o_orderpriority,
			source.o_shippriority,
			source.o_clerk,
			source.o_orderstatus,
			source.o_totalprice,
			source.o_comment
	FROM	source
			CROSS APPLY
			(
				SELECT	MAX(o_orderkey) AS max_o_orderkey
				FROM	dbo.orders
			) AS last_number;
	GO

50,000 records to be added to the table automatically lead to a Lock Escalation and the table is exclusively locked. No other transaction can access the object anymore.

In a partitioned table, this type of lock escalation is of little use if, for example, only one partition is affected during a loading process. In the example shown, it would make sense to exclusively lock the partition for the year 2025; all other partitions are not affected.
To prevent a lock escalation from being automatically applied to the table, the lock escalation parameter must be adjusted for the affected table.

/*
	To optimize loads without locking the whole table it is
	mandatory to set LOCK_ESCALATION to AUTO!
*/
ALTER TABLE dbo.orders
SET	(LOCK_ESCALATION = AUTO);
GO

If the import is carried out again, you can see that the table itself is locked with an IX lock. This means that the data of other partitions (HOBT) can still be accessed for reading and writing.

Although the customer had activated the option for the table, the entire table was still locked. The cause was a non-aligned index on another attribute of the table.

Non Aligned Indexes

In Microsoft SQL Server, a non-aligned index on a partitioned table is an index that is not partitioned in the same way as the table. While the table's data is divided into partitions based on a partitioning key, the non-aligned index is a global structure, spanning all partitions without partition-specific organization. This means that the index does not correspond directly to individual table partitions, and SQL Server treats it as a single entity. Non-aligned indexes can improve performance for certain queries, but they lack the manageability and scalability benefits of aligned indexes, such as partition-level maintenance and switching. Consequently, non-aligned indexes are typically used when query patterns require access patterns that are not aligned with the table’s partitioning scheme.

This non-aligned index prevented the partition-level lock because it spanned all partitions. After we disabled the index, the partition-level lock escalation was performed as expected and the other partitions could be read/written by other transactions.

Thanks for reading!