Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
Nevermind, I discovered the issue. There was an update to a column that was a foreign key to another table. –  Aushin Feb 18 '11 at 20:36
    
please post this as an answer using the Answer Your Question button below. –  Ben Pilbrow Feb 18 '11 at 20:51

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer

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

share|improve this answer
    
This could have also been the potential answer (I accidentally happened upon it myself a moment ago), so thank you. I hadn't thought of this either. –  Aushin Feb 18 '11 at 20:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.