Rebuilding a Table

Issue reorg rebuild to rewrite all rows in a table to new pages, so the table is arranged according to its clustered index (if one exists).

Use reorg rebuild when:

If a table needs to be rebuilt because of a low cluster ratio, it may also need to have its space management settings changed.

If reorg rebuild finds that the current table is used by another session, 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.

When you run it against a table and a partition, reorg rebuild performs the following:
  1. Takes an exclusive table lock
  2. Copies data from old to new pages
  3. Deallocates old data pages
  4. Locks system tables for updates (including sysindexes, sysobjects, syspartitions, and systabstats)
  5. Rebuilds clustered and nonclustered indexes against new data pages
  6. Commits all open transactions
  7. 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 may block processes from accessing information in the system tables for the user tables on which you are running reorg. However, systabstats does not impact this blocking because it is already datarow-locked.

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.