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).
Large I/O is not being selected for queries where it is usually used, and optdiag shows a low cluster ratio for data pages, 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: Procedures.
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.
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.