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

Asked by Aleksey Vitsko
(6195 rep)
Aug 14, 2019, 09:56 AM
Last activity: Aug 14, 2019, 04:31 PM
Last activity: Aug 14, 2019, 04:31 PM