I have a Table1 table that is updated with the following query in small blocks:
update top (1000) Table1 set VarcharColumn1 = 'SomeValue' from Table1 where ID in (select ID from Table2) and VarcharColumn1 is NULL
Table 2 contains 90000 rows, and the total number of rows that must be updated in Table1 is also 90000 (1: 1 relationship).
Even if table rows1 are updated, there is a trigger for tables1, which inserts rows into table Table1History as they were before the update
So, when I update 1000 rows in Table1, 1000 rows are inserted into Table1History
When I update top
100 rowsThere is no escalation of the table lock
I oversee this
Extended Events "lock_escalation" eventand also in
Performance Monitor - SQLServer:Access Methods - Table Lock Escalations / sec
When I refresh the first 1000 or 500 rows, the table lock escalation for Table1 occurs
So I wonder, what is the mechanism or formula that is used by SQL Server to escalate locking to table level ?
Google states that 5000 rows are the threshold, but in my case, 1000 or 500 rows are apparently sufficient to cause Table1 to escalate to Table1