Recommendation needed for reindexing/reorganizing on multiple servers in multiple dbs

  • Lynn Pettis (4/11/2012)


    Reorg is fully logged and can be aborted without losing any work already completed and it olny works at the leaf level so runs faster.

    A rebuild is all or nothing. If something happens to cause a rebuild to abort, the work completed up to that point is lost during the rollback of the rebuild.

    Let me add a little:

    Reorg: Online. Can be aborted while retaining work. Operates only at leaf level. Single-threaded, not parallel.

    Rebuild: Aborting loses work. Offline only except for Enterprise edition, where it can be done online (unless it's a partitioned index or an index on a local temporary table, which is always rebuilt offline), with mostly IS (Intent Shared) locks on the table, which allows queries and updates. Can be done in parallel with Enterprise edition. Also effectively updates statistics with FULLSCAN. Operates on entire index.

    Thus, in certain business cases, such as where the maintenance window for "slow the system" maintenance and "lock tables" maintenance are too short for a full rebuild, completing partial reorgs are of value. In other business cases, I would suggest that the "can be aborted while retaining" work potential benefit is outweighed by the update statistics with FULLSCAN and operates on entire index benefits of rebuilding.

    References:

    http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-10-Rebuilding-Indexes-and-Updating-Statistics.aspx

    http://msdn.microsoft.com/en-us/library/ms189329.aspx

    http://technet.microsoft.com/en-us/library/ms188388.aspx

Viewing post 16 (of 15 total)

You must be logged in to reply to this topic. Login to reply