WolfgangE (12/21/2012)
hi SQLSACT,23 Indexes seems to very very very much for a simple log table. I personally think it's very much for any table. You should check the sys.dm_db_index_usage_stats() too. When checking don't forget that these statistics are resetted when the sqlserver get's restartet.
Of course an update statement can affect the nonclustered indexes. If you update the key column of an index the entry in the index has to updated to and sorted in the new, correct position.
If you have filtered indexes and the new value matches the filter criteria of the index there has to an insert in the index.
Greetings, Wolf
Thanks Wolfgang
Of course an update statement can affect the nonclustered indexes. If you update the key column of an index the entry in the index has to updated to and sorted in the new, correct position.
If you have filtered indexes and the new value matches the filter criteria of the index there has to an insert in the index.
I understand that all Indexes including Nonclustered indexes will be affected by updates
What I meant is that when I run an update statement, sys.dm_db_index_operational_stats shows that an update happened on the clustered index and that an insert happened on the nonclustered index
I think I figured out why this happens though - Looking at the transaction log using fn_dblog, I see LOP_MODIFY_ROW operations on the clustered index, LOP_DELETE_ROWS on the nonclustered index followed by LOP_INSERT_ROWS on the nonclustered index. It seems that SQL updates nonclustered by deleting the old value and then inserting the new value. I wonder if this is always the case for nonclustered indexes??
Regarding the indexes, I know for a fact that most of them have never been queried from but have been inserted into. There are duplicate indexes, there are indexes with the same leading column (not sure if this is a problem), also there are nonclustered indexes where the leading column is the same as the clustering key (not sure if this is a problem).
Thanks