reorg is useful when:
A large number of forwarded rows causes extra I/O during read operations.
Inserts and serializable reads are slow because they encounter pages with noncontiguous free space that must be reclaimed.
Large I/O operations are slow because of low cluster ratios for data and index pages.
sp_chgattribute has been used to change a space management setting (reservepagegap, fillfactor, or exp_row_size) and the change is to be applied to all existing rows and pages in a table, not just to future updates.
The reorg command includes four parameters for carrying out different types and levels of reorganization:
reorg forwarded_rows undoes row forwarding.
reorg reclaim_space reclaims unused space left on a page as a result of deletions and row-shortening updates.
reorg compact both reclaims space and undoes row forwarding.
reorg rebuild undoes row forwarding, reclaims unused page space, and:
Rewrites all rows to accord with a table’s clustered index, if it has one
Rewrites space for data and index partitions.
Works on individual partitions.
Writes rows to data pages to accord with any changes made in space management settings through sp_chgattribute
Drops and re-creates all indexes belonging to the table
The reclaim_space, forwarded_rows, and compact parameters:
Minimize interference with other activities by using multiple small transactions of brief duration. Each transaction is limited to eight pages of reorg processing.
Rewrite space for a single partition.
Provide resume and time options that allow you to set a time limit on how long a reorg runs and to resume a reorg from the point at which the previous reorg stopped. This allows you to, for example, use a series of partial reorganizations at off-peak times to run the reorg command on a large table. See “resume and time options for reorganizing large tables”.
Consider the following before running reorg rebuild:
reorg rebuild holds an exclusive table lock for its entire duration. On a large table this may be a significant amount of time. However, reorg rebuild does everything that dropping and re-creating a clustered index does and takes less time. In addition, reorg rebuild rebuilds the table using all of the table’s current space management settings. Dropping and re-creating an index does not use the space management setting for reservepagegap.
In most cases, reorg rebuild requires additional disk space equal to the size of the table it is rebuilding and its indexes.
The following restrictions hold:
The table specified in the command, if any, must use either the datarows locking or datapages locking scheme.
You must be a system administrator or the object owner to issue reorg.
You cannot issue reorg within a transaction.
