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”).
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 tablename [index_name]
reorg rebuild performs the following when you run it against a table:
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 these locks may block other processes from accessing these system tables. 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 resorted during this index build.
