Use reorg rebuild when:
Large I/O is not being selected for queries where it is usually used, and optdiag shows a low cluster ratio for datapages, data rows, or index pages.
You used sp_chgattribute to change one or more of the exp_row_size, reservepagegap, or fillfactor space management settings and you want the changes to apply not only to future data, but also to existing rows and pages. For information about sp_chgattribute, see the Reference Manual.
If a table needs to be rebuilt because of a low cluster ratio, it may also need to have its space management settings changed (see “Changing space management settings before using reorg rebuild”).
If reorg rebuild finds that the current table is used by another session, it does not wait for this session to end. Instead, it aborts the entire transaction.
reorg rebuild uses a table’s current space management settings to rewrite the rows in the table according to the table’s clustered index, if it has one. All indexes on the table are dropped and re-created using the current space management values for reservepagegap and fillfactor. After a rebuild, a table has no forwarded rows and no unused space from deletions or updates.
The syntax for reorg rebuild is:
reorg rebuild table_name [index_name [partition index_partition_name]
reorg rebuild performs the following when you run it against a table and a partition:
Takes an exclusive table lock
Copies data from old to new pages
Deallocates old data pages
Locks system tables for updates (including sysindexes, sysobjects, syspartitions, and systabstats)
Rebuilds clustered and non-clusterd indexes against new data pages
Commits all open transactions
Releases locks on system tables
If the table is large and has several indexes, the locks for updating system tables can be held for a long time and can block processes from accessing information in the system tables for the user tables on which you are running reorg. However, because systabstats is already datarow-locked, this system table does not impact this blocking.
reorg rebuild builds the clustered index using the with sorted data option, so the data does not have to be re-sorted during this index build.
