Sample Header Ad - 728x90

Why Table Lock Escalation is happening in my scenario

0 votes
2 answers
1515 views
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 Question: 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 extended events lock_escalation
Asked by Aleksey Vitsko (6195 rep)
Aug 14, 2019, 09:56 AM
Last activity: Aug 14, 2019, 04:31 PM