Many of you are familiar with creating a daily and weekly maintenance plans or jobs to scan all indexes and maintain them, by either rebuild or reorganize them based on their fragmentation factor. And yet, when setting the index rebuild order, there are other factors that need to be considered.
These factors can include:
- Index usage: if the index is fragmented, yet there is no usage for it, consider not to rebuild it, or set its re-build order priority to low.
- Table Access: how often is the table accessed compared to other tables (table priority)? If you have several indexes that require rebuild and you know that some are more accessible than the others, prioritize them in the rebuild procedure.
- Parallel rebuild: rebuild indexes from different tables in parallel (as opposed to a sequential build approach). Using SSIS you can run the rebuild index commands on different tables at the same time, minimizing the overall rebuild time. Yes, it requires more development resources to create such process, but the effort benefits by less maintenance time.
- Starvation: Depends on your maintenance window and the number of indexes, it is possible that some indexes are never handled. For example, when the maintenance time window ends before these indexes are handled. To make sure that also these indexes are handled in the following maintenance cycle, your maintenance code must take this type of scenarios in considerations.
