MSSQL 2008R2: Phantom Index Updates

Question

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?

Answer 1

Nevermind, I discovered the issue. There was an update to a column that was a foreign key to another table. Since the foreign key was being updated, the rows referenced by the foreign key had to be updated, causing the index updates on the affected tables.

Answer 2

Do you have indexed views that also reference those tables? An update to those tables would also cause index updates to the indexed views.