A More Effective Selective Index Rebuild/Reorganize Strategy

This is a follow up post to : Why SQL Fragmentation Remains High

As mentioned in this previous post, our dilemma was we were rebuilding every index on a nightly basis, and :

  1. the process proves to be very resource intensive
  2. we don’t see the benefit. The fragmentation for some of the indexes remain high.

After some digging, this is what we found:

Rebuild the index only if there will be at least 1000 pages affected. If < 1000 pages will be affected, fragmentation will not really be affected and potentially would remain high.

This is the script that we now use to selectively identify which indexes to rebuild, which indexes to reorganize, and which indexes to leave alone. This script uses the following criteria in determining which action to take:

  • current fragmentation %
  • number of pages used by the index

The current logic is:

  • reorganize index : if fragmentation is > 10 % but < 30% and number of pages > 1000
  • rebuild index : if fragmentation is > 30 and number of pages > 1000

Continue reading