I was trying to run the following query:
Update A
SET A.Col1 = B.Col1,
...
A.ColN = B.ColN
FROM A INNER JOIN B
ON (A.Col1 = B.Col1)
WHERE B.Col2 = X AND (A.Col1 <> B.Col1 OR ... OR A.ColN <> B.ColN)
The query affects only 4700 rows (and 7 columns) in its update. But it takes over 10 minutes and blocks multiple other users from many other tables.
I checked the query plan and no less than 12 indexes are updated as a result of this query. I know that the legacy databases are overindexed. That isn't my problem right now.
My question is this: 7 of the indexes being updated (and the 3 most costly ones) are not from table A or table B. I've checked all triggers on the table and none of them lead to any effect on the two other tables being updated.
How might this be happening? What mechanism is there that could cause these phantom updates?