Why Table Lock Escalation is happening in my scenario

by voodoo_sh   Last Updated August 14, 2019 10:06 AM

I have a table Table1 that is updated in small chunks using below query:

update top (1000) Table1
    set VarcharColumn1 = 'SomeValue'
from Table1
where ID in (select ID from Table2)
      and VarcharColumn1 is NULL

Additional details:

Table2 has 90000 rows, and total number of rows that need to be updated in Table1, is also 90000 (1 to 1 relationship)

Also when Table1 rows are updated, there is a trigger on Table1 that inserts rows as they were before update, to table Table1History
So this means when I update 1000 rows in Table1, 1000 rows inserted into Table1History


When I update top 100 rows, no Table Lock Escalation happens

I monitor this using Extended Events "lock_escalation" event, and also in Performance Monitor - SQLServer:Access Methods - Table Lock Escalations / sec

When I update top 1000 or 500 rows, Table Lock Escalation IS happening on Table1

So I wonder, what is the mechanism or formula that is used by SQL Server     
to escalate locking to table level ?  

In google it says 5000 rows is the threshold, but obviously in my case 1000 or 500 rows is enough to cause Table Lock Escalation on Table1

Related Questions

What is lock escalation?

Updated June 12, 2017 20:06 PM

DeadLock - Two Process Locked Same Index

Updated November 26, 2018 11:06 AM

sql server lock escalation

Updated April 28, 2017 12:06 PM