January 27, 2012 at 6:52 am
When a Clustered index gets updated, do all the Non Clustered Indexes gets rebuilt ?
My guess is yes. But want to understand the underlying mechanism.
My guess is only those NC Indexes that have had their Clustered Indexes updated will get Reorganized/Removed -- is this a good assumption.
Thanks
January 27, 2012 at 6:56 am
I'm still a little iffy on my sum-total index knowledge, but AFAIK the answer is "no." Updating a clustered index updates the data rows, but does not update the leaf level index rows because those are stored separately from the data rows and pointed to by row locators.
January 27, 2012 at 6:58 am
If you are talking about rebuilding a Clustered Index, then I think yes. I am not sure about that. I am not sure what you meant by updating a clustered Index.
-Roy
January 27, 2012 at 6:58 am
Grizzly Bear (1/27/2012)
When a Clustered index gets updated, do all the Non Clustered Indexes gets rebuilt ?My guess is yes. But want to understand the underlying mechanism.
My guess is only those NC Indexes that have had their Clustered Indexes updated will get Reorganized/Removed -- is this a good assumption.
Thanks
Would the nonclustered indexes need to be rebuilt? After all, they contain pointers back to the clustered indexes, and I'd wonder really if the key that the clustered index organizes around would change?
Seems to be the case that the nonclustered indexes don't need to be rebuilt.
January 27, 2012 at 7:01 am
No I am not talking about Rebuilding. What I am talking about is say a column SSN has a clustered Index on it, And I change the SSN (update it) -- or say change about 10 SSNs. What impact will that have on Non Clustered Index.
Update means a simple change by issuing the UPDATE statement. Not all rows but say just 10 or 15 or even 1. What impact will this have on NC indexes --
January 27, 2012 at 7:05 am
An UPDATE statement really doesn't have any effect except to alter the pointer information and possibly split your index leaves or fragment the indexes (if this causes them to be moved within the actual extents).
Unless you're having severe index problems (like a need to rebuild or reorganize), I wouldn't worry about a 10 row update.
January 27, 2012 at 7:13 am
Grizzly Bear (1/27/2012)
When a Clustered index gets updated, do all the Non Clustered Indexes gets rebuilt?
Just the affected rows in the non-clustered index are maintained (updated) as part of the UPDATE statement processing. So, if SSN is the clustering key, and you change the SSN in 10 data rows, SQL Server automatically updates the (up to) 10 rows per non-clustered index that are affected. Each non-clustered index entry has a pointer back to the clustered index row it is associated with, and it is this pointer that is updated.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 27, 2012 at 7:16 am
Thanks makes sense.
I guess the short answer would be -- IT DEPENDS 🙂
Depends on the number of clustsred index values getting updated. But it definitely does make the NC index move around from page to page (or I should say could make as it could make within the same page) -- there by causing fragmentation.
January 27, 2012 at 7:19 am
If your updates are going to cause a lot of fragmentation, and frequent fragmentation at that, then it is advised you check your database maintenance jobs and verify you are regularly reorganization or rebuilding (as needed) the indexes in question.
Just remember that if you have a lot of indexes on that table that rebuilding the least frequently used index could completely hose the other indexes.
January 27, 2012 at 7:31 am
Brandie Tarvin (1/27/2012)
If your updates are going to cause a lot of fragmentation, and frequent fragmentation at that, then it is advised you check your database maintenance jobs and verify you are regularly reorganization or rebuilding (as needed) the indexes in question.Just remember that if you have a lot of indexes on that table that rebuilding the least frequently used index could completely hose the other indexes.
I'd be curious to know that mechanism. For instance, rebuilding an index that gets used the least, it still either points to the clustered index if it exists, or to the row number of the heap otherwise. How would rebuilding nonclustered indexes affect other nonclustered indexes for instance?
By definition, a clustered index wouldn't be the least used index.
Always willing to learn something new!
January 27, 2012 at 7:45 am
patrickmcginnis59 (1/27/2012)
Brandie Tarvin (1/27/2012)
If your updates are going to cause a lot of fragmentation, and frequent fragmentation at that, then it is advised you check your database maintenance jobs and verify you are regularly reorganization or rebuilding (as needed) the indexes in question.Just remember that if you have a lot of indexes on that table that rebuilding the least frequently used index could completely hose the other indexes.
I'd be curious to know that mechanism. For instance, rebuilding an index that gets used the least, it still either points to the clustered index if it exists, or to the row number of the heap otherwise. How would rebuilding nonclustered indexes affect other nonclustered indexes for instance?
By definition, a clustered index wouldn't be the least used index.
Always willing to learn something new!
Like I said, my index knowledge is iffy, but for example, you have a 73 column table with 45+ NC indexes on it (I actually have this).
Now, I can tell you (even though my devs don't believe me) that not all 45+ indexes are being used. In fact, I'm pretty sure that a good half of them could be dropped without negatively effecting performance. But when the unused indexes are rebuilt (without rebuilding any of the others), my server acts like the world's come to an end. And a look at the other indexes do indicate that fragmentation has occurred in that small window of rebuilding.
So, do not take my words for gospel because your situation might be quite different (and better) than the one I have in mind.
January 27, 2012 at 7:53 am
Brandie Tarvin (1/27/2012)
patrickmcginnis59 (1/27/2012)
Brandie Tarvin (1/27/2012)
If your updates are going to cause a lot of fragmentation, and frequent fragmentation at that, then it is advised you check your database maintenance jobs and verify you are regularly reorganization or rebuilding (as needed) the indexes in question.Just remember that if you have a lot of indexes on that table that rebuilding the least frequently used index could completely hose the other indexes.
I'd be curious to know that mechanism. For instance, rebuilding an index that gets used the least, it still either points to the clustered index if it exists, or to the row number of the heap otherwise. How would rebuilding nonclustered indexes affect other nonclustered indexes for instance?
By definition, a clustered index wouldn't be the least used index.
Always willing to learn something new!
Like I said, my index knowledge is iffy, but for example, you have a 73 column table with 45+ NC indexes on it (I actually have this).
Now, I can tell you (even though my devs don't believe me) that not all 45+ indexes are being used. In fact, I'm pretty sure that a good half of them could be dropped without negatively effecting performance. But when the unused indexes are rebuilt (without rebuilding any of the others), my server acts like the world's come to an end. And a look at the other indexes do indicate that fragmentation has occurred in that small window of rebuilding.
So, do not take my words for gospel because your situation might be quite different (and better) than the one I have in mind.
Thats cool, theres just no mechanism that I know of that would cause this, and if thats the case theres a gap in my knowledge that I can put on my list to eliminate 😉
January 27, 2012 at 8:03 am
patrickmcginnis59 (1/27/2012)
I'd be curious to know that mechanism. For instance, rebuilding an index that gets used the least, it still either points to the clustered index if it exists, or to the row number of the heap otherwise.How would rebuilding nonclustered indexes affect other nonclustered indexes for instance?
It wouldn't.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 27, 2012 at 8:05 am
I had found an article by Paul Randal in the past that helped distinguish what events cause changes to the NC indexes, relates to SQL 2005...
January 27, 2012 at 8:16 am
Leeland (1/27/2012)
I had found an article by Paul Randal in the past that helped distinguish what events cause changes to the NC indexes, relates to SQL 2005...
Nice one thanks!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply