What is the Difference Between Rebuild and Reorganize indexes ; Interview Question


Here I have tried to include all those answers available at web in different posts.

A big thanks to all those who did lots of test run on huge data to just understand difference between re index and reorganize. Before writing I have refer blogs of Paul , Tim , Sunny and many other sql server geeks. I also want to include all those whom I have interviewed and they come with variety of answers.

Syntax :
Rebuild : ALTER INDEX ALL ON  table_name REBUILD
Reorganize : ALTER INDEX ALL ON  table_name REORGANIZE


S.no
REBUILD
REORGANIZE
1This process drops the existing Index and Recreates the indexThis process doesn’t drop indexes; It physically reorganizes the leaf nodes of the index.
2Syntax: ALTER INDEX ALL ON table_name REBUILDSyntax: ALTER INDEX ALL ON table_name REORGANIZE
3Index should be rebuild when index fragmentation is great than 30% Index should be reorganized when index fragmentation is between 10% to 30%
4Index rebuilding process uses more CPU and it locks the database resourcesIndex reorganize process uses less CPU and it doesn’t the database resources
5Rebuilding an index can be executed online or offlineReorganizing an index is always executed online
6A nice thing about rebuilding an index is that you can change the setting for index properties like the fill factor and index paddingIndex options cannot be specified when reorganizing an index

7Requires ALTER permission on the table or view or  User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database rolesRequires ALTER permission on the table or view or  User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles
8REBUILD locks the table for the whole operation period except in Enterprise Edition with ONLINE = ONREORGANIZE doesn't lock the table.
9It is changing the whole B-tree, recreating the indexIt is a process of cleaning, organizing, and defragmenting of only  "leaf level" of the B-tree 
10REBUILD will automatically rebuild all index-column statisticsStatistics are NOT updated with a reorganization
11This reclaims disk space by compacting the pages in new indexIt works in two phases – compaction and defrag
12Rebuild does require extra space as same the size of index Reorganize essentially just swaps one page with another and thus does not require free space 
13Rebuild can fix extent fragmentation as wellReorganize can remove almost all of the logical fragmentation but it cannot necessarily fix extent fragmentation
14Index (clustered or non-clustered) cannot be built online if it contains LOB data (text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data type columns). Its always online operation.
15If an index has multiple partitions, then you cannot rebuild a single partition online You can reorganize a single index partition online
16After rebuild statistics on non-indexed columns doesn’t get re-computed Statistics are NOT updated with a reorganization
17Rebuild is an atomic operationReorganize can be stopped while it is occurring and you won't lose the work it has already completed
18Rebuild indexes always generates similar amount of  t-log for same size indexFor large, heavily fragmented indexes the reorganization operation will generate more t-log as compared to less fragmented index
19REBUILD requires enough free space in the database to accommodate the new index REORGANIZE only requires 8KB of additional space in the database
20REBUILD can use multiple CPUs so the operation runs fasterREORGANIZE is always single-threaded
21REBUILD can use minimal-logging to reduce transaction log growthREORGANIZE is always fully logged, but doesn’t prevent transaction log clearing.
22An index rebuild will always rebuild the entire index, regardless of the extent of fragmentationAn index reorganize will only take care of the existing fragmentation
23SQL 2000 use DBCC DBREINDEXSQL 2000 use DBCC INDEXDEFRAG
24Even If the index spans multiple data files, it will be taken careIf the index spans multiple data files, reorganizing will only reorder pages within the same file, it won’t move pages between files



 References:

12 comments:

  1. Index should be rebuild when index fragmentation is great than 40%

    But Microsoft recommends 30%.

    ReplyDelete
    Replies
    1. Thanks Sarjen, corrected the recommendation

      Delete
  2. Nice post to understand Reorganise and rebuild.. well done.

    ReplyDelete
  3. Nice Article...! Congratulations

    ReplyDelete
  4. Good one, easy to understand for beginners as well. Thanks

    ReplyDelete