Otdfbt

Multi tool use

Taxi Services at Didcot

Soft question: Examples where lack of mathematical rigour cause security breaches?

Using a found spellbook as a Sorcerer-Wizard multiclass

How can drunken, homicidal elves successfully conduct a wild hunt?

How to project 3d image in the planes xy, xz, yz?

What are the peak hours for public transportation in Paris?

Is open-sourcing the code of a webapp not recommended?

What makes an item an artifact?

Are there downsides to using std::string as a buffer?

Do simulator games use a realistic trajectory to get into orbit?

How to retract an idea already pitched to an employer?

Should I compare a std::string to "string" or "string"s?

Confusion about off peak timings of London trains

Payment instructions allegedly from HomeAway look fishy to me

Can an Aarakocra use a shield while flying?

What's the name of this light airplane?

Trapping Rain Water

Should an arbiter claim draw at a K+R vs K+R endgame?

What should the arbiter and what should have I done in this case?

Was the Tamarian language in "Darmok" inspired by Jack Vance's "The Asutra"?

Words that signal future content

Chemmacros scheme translation

Why doesn't Adrian Toomes give up Spider-Man's identity?

What risks are there when you clear your cookies instead of logging off?

MSSQL 2008R2: Phantom Index Updates

SQL Server 2000 tablesDoes index fill factor become meaningless on large tables?Mysterious timeouts in MSSQL 2000null values stored in databaseLarge script, many inserts and deletes: There is insufficient system memory to run this queryRow size, indexes and varchar(max)sql server shard lock issueSQL Server normalizing for performanceMS SQL after upgrade from 2014 to 2016 JOIN takes longerSQL change column datatype in production

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

1

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?

sql-server update

share|improve this question

edited May 21 at 13:46

window.document

1436

asked Feb 18 '11 at 20:02

AushinAushin

1456

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

1

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?

sql-server update

share|improve this question

edited May 21 at 13:46

window.document

1436

asked Feb 18 '11 at 20:02

AushinAushin

1456

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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?

sql-server update

share|improve this question

edited May 21 at 13:46

window.document

1436

asked Feb 18 '11 at 20:02

AushinAushin

1456

 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)

share|improve this question

edited May 21 at 13:46

window.document

1436

asked Feb 18 '11 at 20:02

AushinAushin

1456

share|improve this question

edited May 21 at 13:46

window.document

1436

asked Feb 18 '11 at 20:02

AushinAushin

1456

edited May 21 at 13:46

window.document

1436

edited May 21 at 13:46

window.document

1436

asked Feb 18 '11 at 20:02

AushinAushin

1456

asked Feb 18 '11 at 20:02

AushinAushin

1456

2 Answers
2

active

oldest

votes

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.

share|improve this answer

edited Feb 18 '11 at 21:04

answered Feb 18 '11 at 20:54

AushinAushin

1456

add a comment | 

0

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

answered Feb 18 '11 at 20:29

squillmansquillman

35.3k980134

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "2"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f237441%2fmssql-2008r2-phantom-index-updates%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

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.

share|improve this answer

edited Feb 18 '11 at 21:04

answered Feb 18 '11 at 20:54

AushinAushin

1456

add a comment | 

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.

share|improve this answer

edited Feb 18 '11 at 21:04

answered Feb 18 '11 at 20:54

AushinAushin

1456

add a comment | 

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

edited Feb 18 '11 at 21:04

answered Feb 18 '11 at 20:54

AushinAushin

1456

share|improve this answer

edited Feb 18 '11 at 21:04

answered Feb 18 '11 at 20:54

AushinAushin

1456

answered Feb 18 '11 at 20:54

AushinAushin

1456

answered Feb 18 '11 at 20:54

AushinAushin

1456

0

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

answered Feb 18 '11 at 20:29

squillmansquillman

35.3k980134

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

0

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

answered Feb 18 '11 at 20:29

squillmansquillman

35.3k980134

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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
  

  
  
  
  

add a comment | 

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

answered Feb 18 '11 at 20:29

squillmansquillman

35.3k980134

share|improve this answer

answered Feb 18 '11 at 20:29

squillmansquillman

35.3k980134

answered Feb 18 '11 at 20:29

squillmansquillman

35.3k980134

answered Feb 18 '11 at 20:29

squillmansquillman

35.3k980134