Rebuild Index:
Throws away the complete index and rebuilds it from scratch.
This can be a long process but it is more complete then defrag/reorganize index.
Best used if the fragmentation is heavy (over 30%).
Can be done online in SQL2005 enterprise edition otherwise it means that the table is blocked
during the rebuilding.
Statistics are recalculated.
Defragment\Riorganize Index:
- Defrag/reorganize index : is faster then rebuild index but less complete.
You can use it when index fragmentation is under 30%.
Can be done online, meaning people can use the table when doing it.
Statistics are NOT recalculated
Sift Index Table
SIFT-tables are normal tables for SQL, so they will also be rebuild or defragged.
The 0-SIFT-records will NOT be deleted.
--> SSI utility delete 0 Sift table
SHEDULING
REINDEX FULL
Best use rebuild index once a week (e.g. Sunday)
DEFRAG
You can use defrag/reorganize during the night on weekdays or allfdays
--> If you use defrag, you also need to recalculate the statistics.
Of course the maintenance plans you make vary according to DB-size and time-windows
when you can do the actions.
STATISTICS
Recalculate statistics is best done every night (except in case of a rebuild index).
Sift Index Table
All nights
SEQUENCE SCHEDULING
SiftTable 0 delete
Defrag
Statistics
Reindex Full