Incremental Reorganization

Adaptive Server 15.7 SP100 includes a new parameter—defrag—for the reorg command, which lets you schedule and resume reorganization, also allowing concurrent reads or writes on the data being reorganized.

reorg defrag locks each row or page, as per the locking scheme of the object, and begins and commits a transaction for every data chunk processed. The data reorganization space requirement does not exceed the size of an allocation unit (256 data pages). Index updates for the reorganized data do not consume extra space. For every data partition undergoing incremental reorganization, a row is stored in sysattributes.

• Running reorg defrag
  reorg defrag executes multiple reorganization transactions one right after the other, compared with traditional reorg, which reorganizes the entire table data in a single transaction. You can specify a time interval during which it reorganizes the data. This allows other processes to run concurrently without affecting reorganization.
• Checking the Reorganization Status
  Use information from sp_helpdefrag or the defrag_status built-in function to decide if reorganization must be resumed on a table or if it should start from the beginning.
• Clearing reorg defrag Information from sysattributes
  For every data partition undergoing incremental reorganization, a row is stored in sysattributes. Use the dbcc command zapdefraginfo to delete this information from sysattributes before performing a downgrade.
• Logging Behavior
  Default incremental reorganization logging includes the extent allocation, extent version update, index row deletion, index row insertion, extent deallocation, and scan position update, in that order.
• Restrictions
  There are several restrictions that apply to reorg defrag.