2 added 96 characters in body
source | link

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

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

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

1
source | link

Why Table Lock Escalation is happening in my scenario

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