How to avoid Table Lock Escalation?

I've got a task to update 5 million rows in a production table, without locking down the whole table for extended time

So, I used approach that helped me before many times - updating top (N) rows at a time with 1-N second interval between chunks

This time started with update top (1000) rows at a time, monitoring the Extended Events session for lock_escalation events in the process

lock_escalation showed up during each update operation, so I started lowering row count per chunk 1000 -> 500 -> 200 -> 100 -> 50 rows and so on down to 1

Before (not with this table, and for delete operations - not update), lowering row count to 200 or 100, helped to get rid of lock_escalation events

But this time, even with 1 row per 1 update operation, table lock_escalation still shows up. Duration of each update operation is about the same, regardless if its 1 row or 1000 rows at a time

How to get rid of table lock escalations in my case ?

@@TRANCOUNT is zero

Extended event:

Code:

```
set nocount on

declare
@ChunkSize int = 1000, -- count rows to remove in 1 chunk
@TimeBetweenChunks char(8) = '00:00:01', -- interval between chunks

@Start datetime,
@End datetime,
@Diff int,

@MessageText varchar(500),

@counter int = 1,
@RowCount int = 1,
@TotalRowsToUpdate bigint,
@TotalRowsLeft bigint


-- total row count to update
set @TotalRowsToUpdate = (select count(*)
from [Table1]
join [Table2] on
btid = tBtID
where btStatusID = 81)

set @TotalRowsLeft = @TotalRowsToUpdate
set @MessageText = 'Total Rows to Update = ' + cast(@TotalRowsLeft as varchar) raiserror (@MessageText,0,1) with nowait
print ''

-- begin cycle
while @RowCount >

If we look at the actual plan, the current query is reading too much data from the table to be updated. This is from the index seek on BoxTrackInfo:

This is an index seek on btid for each row that comes out of the scan of BlueTrackEvents. Update locks are acquired as btStatusID is checked to see if the row qualifies for the update. Only 1,401 rows qualify for the update, but many more locks are taken in the process - resulting in lock escalation to the table level.

You really want a different plan shape - to seek into the BoxTrackInfo table on btStatusID and then join to BlueTrackEvents, which should acquire significantly less locks. To that end, adding an index like this should help:

CREATE NONCLUSTERED INDEX IX_btStatusID 
ON dbo.BoxTrackInfo (btStatusID)
INCLUDE (btType);

This should more efficiently locate qualifying rows, hopefully allowing the update to complete without lock escalation.

As a side note, the current execution plan validates the foreign key constraint on btStatusID using a merge semi-join:

This is probably not a big deal in your case, since there are only 267 rows in the LBoxTrackStatus table. If that table were larger, you might consider adding a LOOP JOIN or FAST 1 hint to the query in order to get nested loops FK validation. See this post for details:

Why am I getting a snapshot isolation issue on INSERT?

StackExchange Database Administrators Q#276314, answer score: 9