Skip Indexes with <= N reads

[scsimon]

One way to exclude indexes is to use @Indexes = 'ALL_INDEXES, -Db1.Schema1.Tbl1.Idx1' (thanks @TomVergote for highlighting what i missed in the documentation). However, it would be nice if you could skipped indexes which haven't been used for reads. In most cases, the DBA should do spring cleaning on their database and remove these unused indexes. Sometimes the DB is vendor supplied and agreements prevent such changes to the DB. These indexes can grow large, and extend the maintenance despite never being used. The number of indexes in this case could be hundreds.

Some thoughts are:

• A parameter to skip any index that has <= N reads via sys.dm_db_index_usage_stats
• A threshold parameter for the age of the stats. If a SQL Server restart has occurred, many indexes may have 0 reads, thus the user may only want this option enabled if the index usage stats cover at least X numbers of days to cover their business cycle.
• Stuff that smart people will think about that is beyond me :)

[rajuvd]

I'm looking for the options to exclude some tables from all the databases from UPDATE_STATISTICS command. Is there a way I'm missing, I couldn't find in the documentation.

[JogchumRooda]

I think it would also be suitable for other cases, for example when you are using a third party software which has all kinds of indexes that are not used but cannot be deleted because the updates of the software will then fail.

[drstonephd]

Perhaps a separate tool to disable (not delete) unused indexes?

[JogchumRooda]

Perhaps a separate tool to disable (not delete) unused indexes?

Would be an idea. Can we set the Index Maintenance job to skip disabled indexes?

[SirSmartyPants]

We've written a procedure to generate a list of unused indexes for a specific database and/or table, which is then passed into the IndexOptimize proc, but it would be preferable to be able to skip unused indexes for all databases.