3

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?

  • 2
    If you've looked through the documentation and you don't see such an option, you could submit a feature request. – Aaron Bertrand Jul 11 '18 at 21:55
  • I didn’t @AaronBertrand but wanted to make sure, and weed out any gotchas. Thanks! – scsimon Jul 11 '18 at 21:58
  • 1
    Could you please define "not being used" a bit closer? If the indexes really aren't being used (aka: no data inserted, deleted, modified), then Ola's scripts shouldn't touch the indexes when reorganizing and/or rebuilding. (> 1000 pages / 5 % fragmentation = REORGANIZE; > 1000 pages / 30 % fragmentation = REBUILD) – hot2use Jul 12 '18 at 11:27
  • @hot2use specifically reads. As you stated, fragmentation thresholds would suffice for indexes which aren’t written to. – scsimon Jul 12 '18 at 11:33
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.