Sql-server – Ola Hallengren Index Optimize – Skip “unused” Indexes

ola-hallengrensql server

Is there a way to skip "unused" indexes based on the total reads of that index during an index maintenance w/ Ola's scripts?

I understand that

  • Index stats are only since the last restart, so monitoring over a business cycle is important
  • Skipping a reorg or rebuild on an "unused" index which may be needed later, even once, in the business cycle could cause performance issues
  • Everyone's thinking "why don't you just remove it"

To answer the latter, it's a vendor supplied application w/ an OLTP DB of ~2TB. To stay compliant, we can't change much and I can't drop their indexes among other things. Thus, I thought it'd be beneficial to reduce the maintenance time by skipping these indexes once I've confirmed they aren't ever used. This would be beneficial for situations where, like this, my hands are mostly tied.

Follow on for comments would be if this isn't able to be done, is it worth tossing to Ola as an enhancement?

Best Answer

I don't think there is such an option, and you could submit a feature request (or a pull request) to Ola, but you can however submit a list of indexes that you want to exclude.

For example if you pass ALL_INDEXES, -Db1.Schema1.Tbl1.Idx1 into the @indexes parameter the script will skip the index Idx1 on the object Schema1.Tbl1 in the database Db1.

The @indexes parameter is nvarchar(max) so you should be able to pass as many indexes as you like.

I realize that this may become very tedious to maintain, but you should be able to generate the list from the query you use to determine unused indexes.

This approach would however allow for a more fine-grained control over which indexes you skip and this way you could avoid skipping indexes that are only used every once in a while or you could also create different jobs so you skip large unused indexes in your normal job but still defragment some of them when you have a larger maintenance window etc.

Related Question