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 :
- the process proves to be very resource intensive
- 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
10 Comments
