0

LOCK_ESCALATION= AUTO.

In one of our Data warehousing product we use partitioned tables. They are partitioned based on an integer column. We have implemented partitioning here so we can easily switch data across tables.

Environment Details:

SQL Version: SQL Server 2016
Table: FACT
Approx Size: 100-500 GB
No of Partitions: 100
Partition Column: RunID (int)

Activity: Parallel ETL Processing
Applications Involved in ETL: SSIS package

We have a SSIS Package which is written to process in parallel for 3 RunIDs (3 Partitions). These 3 runs are few minutes/hours apart from each other.

We have faced a situation where we observe a time-out when below condition occurs. Below is the observation: Run 3 (Truncate seems to be blocked for an hour) Run 2 (Bulk Insert, this is timed-out) We Suspect either Run2 or Run1 is conflicting with Run3, eventually causing a time-out for Run2.

Run 3 Run 2
Truncate with partition Bulk insert
partion 3 Partion 2

To avoid this conflict, we are considering to set the Lock Escalation of this partitioned table to AUTO. So, the locks would remain at HoBT level. While we are processing concurrently for 3 different Partitions.

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver16#set--lock_escalation---auto--table--disable--

Before Implementing, we wanted an opinion to understand the risks associated by using this configuration for a particular table which is only Queried/Processed for one partition at a time. Could this change introduce deadlocks? Since the early adopters of this feature have experienced deadlocks:

Paul Randal: https://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-2330-lock-escalation/

Brent Ozar: https://www.brentozar.com/archive/2017/11/partition-level-locks-confusing/

2
  • 1
    From the very ancient MS document "HoBT-level locks usually increase concurrency, but introduce the potential for deadlocks when transactions that are locking different partitions each want to expand their exclusive locks to the other partitions. In rare instances, TABLE locking granularity might perform better." I doubt it's a major problem, unless you are seeing actual deadlocks? And you would anyway only see them if working on multiple partitions at once in a single batch. Commented Jun 6, 2023 at 20:26
  • 1
    "We Suspect either Run2 or Run1 is conflicting with Run3, eventually causing a time-out for Run2." You shouldn't guess. The blocking may be between the ETL and readers. Commented Jun 6, 2023 at 21:28

1 Answer 1

0

The answer is YES worth it but consider the trade off scenarios and also do benchmarking before and after as well as non prod thorough testing.

Setting the LOCK_ESCALATION option to AUTO for your partitioned table could help reduce lock contention and improve concurrency for your parallel ETL processing. By keeping the locks at the HoBT (Heap or B-Tree) level, it allows for finer-grained locking at the partition level.

However, as you mentioned, there are some potential risks and considerations associated with this change. While partition-level locks can help reduce contention and improve concurrency, they may also introduce the possibility of deadlocks. With finer-grained locking, there is a chance of increased lock conflicts between concurrent processes accessing different partitions. test and monitor system for potential deadlock scenarios after making this change.

Additionally, finer-grained locking at the partition level can result in increased memory overhead due to the maintenance of individual locks for each partition. If you have a large number of partitions Ensure enough memory to accommodate the change

benchmark and compare the performance impact before and after implementing this change. Consider temporarily disabling partition-level locking (LOCK_ESCALATION = TABLE) during maintenance operations like index rebuilds or partition switching to optimize these operations.

Test this change in a non-prod environment .

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.