Rebuild Index Task
1. Rebuilds the indexes specified in the task configuration
2. As a side effect all index based statistics are updated with full scan
Limitations
1. Indexes are rebuilt regardless of the fragmentation level
2. Does not support partition level rebuilds for partitioned tables or indexes
Free space options -specifies the free space to leave in the leaf level of the index pages during the rebuild
1.Default free space per page - maintains the currently configured fill factor value for the indexes
2. Change free space per page to -specifies that the free space will be changed to the configured value as a part of the rebuild.
Sort in tempdb option - specifies to store intermediate sort runs in the tempdb database rather than in the database the index is being rebuilt in
Offer the ability to rebuild indexes online if using enterprise edition
- for indexes that can not be rebuilt online, this provide the option to rebuild offline or do nothing at all
Reorganize Index Task
This is reorganizes indexes but does not include a update of the index statistics like the rebuild Index task does
=> As a result, when the reorganize index task is used a subsequent update statistics task should also be included to update the column statistics of the database
1. Rebuilds the indexes specified in the task configuration
- When multiple database are selected in a single task, all indexes in the selected database will be rebuilt
- When a single database is selected, individual objects can be selected in that database to rebuild all of the indexes on the selected objects
2. As a side effect all index based statistics are updated with full scan
Limitations
1. Indexes are rebuilt regardless of the fragmentation level
2. Does not support partition level rebuilds for partitioned tables or indexes
Free space options -specifies the free space to leave in the leaf level of the index pages during the rebuild
1.Default free space per page - maintains the currently configured fill factor value for the indexes
2. Change free space per page to -specifies that the free space will be changed to the configured value as a part of the rebuild.
Sort in tempdb option - specifies to store intermediate sort runs in the tempdb database rather than in the database the index is being rebuilt in
Offer the ability to rebuild indexes online if using enterprise edition
- for indexes that can not be rebuilt online, this provide the option to rebuild offline or do nothing at all
Reorganize Index Task
This is reorganizes indexes but does not include a update of the index statistics like the rebuild Index task does
=> As a result, when the reorganize index task is used a subsequent update statistics task should also be included to update the column statistics of the database
No comments:
Post a Comment