RowModCtr Not Updating ?

I would like to use the RowModCtr to evaluate how quickly several indexes ar
e
changing on a table but it seems I'm only witnessing an increment on a
clustered index. Suppose I have this:
CustInfoTable
CustNum
SSN
FName
LName
... with indexes like ...
clustered (PK) on custnum
non-clustered on ssn
non-clustered on fname, lname
Let's pretend I UPDATE ssn, I see the rowmodctr in sysindexes incremented
against the custnum (pk), not against the index on ssn. How does SQL Server
figure the number of rowmods against the index SSN ? Again, if I were to
UPDATE one row against fname, lname I see the reference in sysindexes
incrementing against the clustered (pk).
AutoUpdate Stats is off.
Can anyone shine light on this behavior? I am ultimately am trying to
figure out how quickly fragmentation is occuring on each index of a table so
that I can pin-point which to DBREINDEX during my maintenance window rather
than just blindly doing all.
Thanx.
-CqlboyI am pretty sure that the only one that counts in SQL2000 is the one on the
clustered index or heap. In 2005 each column is tracked individually and
thus a lot more accurate and useful overall.
Andrew J. Kelly SQL MVP
"Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
news:97050225-F44A-4DC3-A11B-0C424D3F1099@.microsoft.com...
>I would like to use the RowModCtr to evaluate how quickly several indexes
>are
> changing on a table but it seems I'm only witnessing an increment on a
> clustered index. Suppose I have this:
> CustInfoTable
> CustNum
> SSN
> FName
> LName
> ... with indexes like ...
> clustered (PK) on custnum
> non-clustered on ssn
> non-clustered on fname, lname
> Let's pretend I UPDATE ssn, I see the rowmodctr in sysindexes incremented
> against the custnum (pk), not against the index on ssn. How does SQL
> Server
> figure the number of rowmods against the index SSN ? Again, if I were to
> UPDATE one row against fname, lname I see the reference in sysindexes
> incrementing against the clustered (pk).
> AutoUpdate Stats is off.
> Can anyone shine light on this behavior? I am ultimately am trying to
> figure out how quickly fragmentation is occuring on each index of a table
> so
> that I can pin-point which to DBREINDEX during my maintenance window
> rather
> than just blindly doing all.
> Thanx.
> -Cqlboy|||Thanx. Yes, it seems that SQL Svr 2000 only increments the clustered or hea
p
and the rowmodctr value for other indexes is not maintained at all. Bummer.
-Cqlboy
"Andrew J. Kelly" wrote:

> I am pretty sure that the only one that counts in SQL2000 is the one on th
e
> clustered index or heap. In 2005 each column is tracked individually and
> thus a lot more accurate and useful overall.
> --
> Andrew J. Kelly SQL MVP
>
> "Cqlboy" <Cqlboy@.discussions.microsoft.com> wrote in message
> news:97050225-F44A-4DC3-A11B-0C424D3F1099@.microsoft.com...
>
>