Reorg Rebuild

The  reorg rebuild is use in the following condition :

1.       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.                                                                                                                                         

2.       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.

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

Reorg rebuild on a TABLE

Reorg rebuild uses a table's current space management settings to rewrite the rows in the table according to the table's clustered index.

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.

Reorg rebuild table syntax:-

reorg rebuild tablename [index_name]

Steps tobe consider before  running  reorg rebuild on a TABLE:-

1)Set the database option select into/bulkcopy/pllsort to true and run checkpoint in the database.                                                                                                

2)Make sure that additional disk space, equal to the size of the table and its indexes, is available.

Other comments
1)When reorg rebuild rebuilds a table, it rewrites all table and index rows according to the table's current settings for reservepagegap, fillfactor, and exp_row_size.           These properties all affect how quickly inserts cause a table to become fragmented, as measured by a low cluster ratio.
2)If it appears that a table quickly becomes fragmented and needs to be rebuilt too frequently, it may be a sign that you need to change the table's space management settings before you run reorg rebuild, by  using  sp_chgattribute .

Performance characteristics

Running reorg against a table can have a negative effect on performance of concurrent queries.

Reorg rebuild on a INDEX
The reorg rebuild command allows you to rebuild individual indexes while the table itself is accessible for read and update activities.
Reorg rebuild  index syntax:-

reorg rebuild table_name index_name 

Steps tobe  consider  before  running  reorg rebuild on a INDEX:-

1) You do not need to set select into to rebuild an index.
2) You must be the table owner or the Database Owner, or having SA  privileges.
3) You needs space for the pages copied on each transaction since rebuilding an index works in small transactions, and deallocates pages once they are copied
4) You can rebuild the index on a table while transaction level scans (dirty reads) are active 
5)  If  we  omit the index name, the entire table is rebuilt.
6)If we  specify an index name , only that index is rebuilt.

Other comments

The reorg command applies only to tables using datarows or datapages locking. You cannot run reorg on a table that uses allpages locking.
You cannot run reorg on a text index, the name from sysindexes associated with a text chain.
You cannot run reorg within a transaction.
You can do a dump tran on a table after rebuilding its index. However, you cannot do a dump tran if the entire table has been rebuilt.
You can rebuild the index for systabstats, but you cannot run reorg rebuild on the table itself.
Reorg rebuild may not rebuild those parts of the index that are already well clustered and have the desired space utilization.

Performance characteristics

Index scans are faster after you run reorg.
Reorg rebuild accomplishes everything that dropping and re-creating a clustered index does and takes less time.