February 7, 2012 at 12:33 pm
Going back to last weeks questions on Clustered Index Update.
I found out that when the Non Clustered Index gets updated the count of user_updates in sys.dm_db_index_usage_stats gets increased by 1 for the Clustered Index as well.
The impression I get is this should happen only when Clustered Index gets updated. Am I wrong (I seem to be -- at least from the stats that I obtained).
Thoughts -- on why would this happen?
February 7, 2012 at 12:55 pm
The clustered index is the table, it has every single column in the table in it. Hence any update to the table at all updates the clustered index and zero or more nonclustered indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2012 at 1:03 pm
As usual you make sense Gail.
In my opinion it is misleading. Mind you this is the very number that led me to think that Clustered Index was getting updated and post that Clustered Index question -- till I dug deeper and found this out.
Thanks
February 7, 2012 at 1:13 pm
Why do you say it's misleading?
If I have a nonclustered index as such - index key (col1, col2) INCLUDE (col3, col4) and column3 is updated, has that nonclustered index received an update or not?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2012 at 1:23 pm
But why would the count for Clustered Index increase by 1 if I update a NC index?
February 7, 2012 at 1:29 pm
February 7, 2012 at 1:36 pm
Grizzly Bear (2/7/2012)
But why would the count for Clustered Index increase by 1 if I update a NC index?
Can you update a nonclustered index without updating the table that the index is on?
Back to basics: Index on a table, Index key (col1, col2) INCLUDE (col3, col4) If column3 is updated, has that nonclustered index received an update or not?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2012 at 1:36 pm
Jim_K (2/7/2012)
I might be wrong about this, but on a table that has a clustered index, the nonclustered index references back to the clustered index[/url]. If its a heap (no clustered index) there is an underlying rowid instead.Perhaps this is why something could get incremented? Gail would know 😀
Nope, nothing to do with that. Other way round in fact.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2012 at 1:44 pm
Column3 is part of NC Index -- hence rightfully gets incremented.
Technicalities aside -- Clustered Index is a seperate entity and hence its count should not increase (IMO).
Putting another simple question -- if I updated Clustered Index Column as well as NC columns -- how will I know how many times my CI has been updated vis-a-vis the NC index? I want to know the answer to the above question -- just by looking at the table.
February 7, 2012 at 1:56 pm
Grizzly Bear (2/7/2012)
Column3 is part of NC Index -- hence rightfully gets incremented.
Correct. Now, consider the clustered index which implicitly includes every single column in the table (the clustered index contains the actual data rows at the leaf level). Just as with a nonclustered index, if the include columns (the rest of the table) get updated, the update count must increment.
Technicalities aside -- Clustered Index is a seperate entity and hence its count should not increase (IMO).
It is not a separate entity. It is the table. Since it is impossible to update a nonclustered index without updating the table, the update count for the table (the clustered index) has to be incremented. .
if I updated Clustered Index Column as well as NC columns -- how will I know how many times my CI has been updated vis-a-vis the NC index? I want to know the answer to the above question -- just by looking at the table.
That's not a valid question, you cannot update a nonclustered index without updating the table itself. The nonclustered indexes don't store data independently, they are based on the table itself.
If an update occurs, it must first be done to the table (the clustered index), then it must be done to any nonclustered indexes that have the column as a key or include column. Each of those updates (cluster and 0 or more nonclusters) get their update counts incremented.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply