sys.dm_db_index_usage_stats question

Question

sys.dm_db_index_usage_stats question

Grizzly Bear

Ten Centuries

Points: 1192
More actions
February 7, 2012 at 12:33 pm

#254970

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?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grizzly Bear Ten Centuries Points: 1192 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grizzly Bear Ten Centuries Points: 1192 More actions · Answer 4

But why would the count for Clustered Index increase by 1 if I update a NC index?

Jim_K SSCrazy Points: 2418 More actions · Answer 5

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 😀

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grizzly Bear Ten Centuries Points: 1192 More actions · Answer 8

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass