asked
Negative effect of unused indexes maintenance
I am using the `[sys].[dm_db_index_usage_stats]` view to find unused indexes. I am looking for all of the following fields to be equal to `0`: [user_scans] [user_seeks] [user_lookups] So, I have a list with indexes that have not been used since the indexes statistics were reset but are constantly updated. I am trying to create a list with negative effects that such indexes could have. For example: - they cost storage - depending on the CRUD operations over the table fragmentation could appear - they are updated Could anyone tell for other negative effects that these indexes can cause? For example: - is indexes maintenance delaying CRUD operations over the table - is an additional work done for logging indexes maintenance - can indexes maintenance cause locking
Seems like you have most of it covered... but I'd like to add -- backup/restores and consistency checks (you do this, right?) As far as the maintenance goes, it depends! There are 2 option REORGANIZE and REBUILD. The REORGANIZE option doesn't cause much locking because it moves one page at a time. It acquires a shared lock of a row while it's being moved to a new location but it's a fast operation. The most intrusive out of these two is REBUILD. It creates a whole new structure of the index. If you're on the EE, then you can use the ONLINE option which allows your server to avoid locking (not all of it but most!) If you are on the SE, then during the nonclustered index rebuild -- you can READ the table but you cannot WRITE into it and during the clustered index rebuild you cannot READ or WRITE from/into the table. If you would like to learn more about the index maintenance, I strongly suggest to watch [this video][1] by Paul Randal. Let me know if you have more questions! [1]:
https://www.youtube.com/watch?v=p3SXxclj_vg
As you say, he covered most of them in the question. Depending on the situation, I think the negative impact on inserts and updates is generally the most important one (though the space they use may be significant if space is scarece).
15 People are following this question.
