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
|
1 | This process drops the existing Index and Recreates the index | This process doesn’t drop indexes; It physically reorganizes the leaf nodes of the index. |
2 | Syntax: ALTER INDEX ALL ON table_name REBUILD | Syntax: ALTER INDEX ALL ON table_name REORGANIZE |
3 | Index should be rebuild when index fragmentation is great than 30% | Index should be reorganized when index fragmentation is between 10% to 30% |
4 | Index rebuilding process uses more CPU and it locks the database resources | Index reorganize process uses less CPU and it doesn’t the database resources |
5 | Rebuilding an index can be executed online or offline | Reorganizing an index is always executed online |
6 | A nice thing about rebuilding an index is that you can change the setting for index properties like the fill factor and index padding | Index options cannot be specified when reorganizing an index |
7 | Requires 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 | Requires 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 |
8 | REBUILD locks the table for the whole operation period except in Enterprise Edition with ONLINE = ON | REORGANIZE doesn't lock the table. |
9 | It is changing the whole B-tree, recreating the index | It is a process of cleaning, organizing, and defragmenting of only "leaf level" of the B-tree |
10 | REBUILD will automatically rebuild all index-column statistics | Statistics are NOT updated with a reorganization |
11 | This reclaims disk space by compacting the pages in new index | It works in two phases – compaction and defrag |
12 | Rebuild 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 |
13 | Rebuild can fix extent fragmentation as well | Reorganize can remove almost all of the logical fragmentation but it cannot necessarily fix extent fragmentation |
14 | Index (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. |
15 | If an index has multiple partitions, then you cannot rebuild a single partition online | You can reorganize a single index partition online |
16 | After rebuild statistics on non-indexed columns doesn’t get re-computed | Statistics are NOT updated with a reorganization |
17 | Rebuild is an atomic operation | Reorganize can be stopped while it is occurring and you won't lose the work it has already completed |
18 | Rebuild indexes always generates similar amount of t-log for same size index | For large, heavily fragmented indexes the reorganization operation will generate more t-log as compared to less fragmented index |
19 | REBUILD requires enough free space in the database to accommodate the new index | REORGANIZE only requires 8KB of additional space in the database |
20 | REBUILD can use multiple CPUs so the operation runs faster | REORGANIZE is always single-threaded |
21 | REBUILD can use minimal-logging to reduce transaction log growth | REORGANIZE is always fully logged, but doesn’t prevent transaction log clearing. |
22 | An index rebuild will always rebuild the entire index, regardless of the extent of fragmentation | An index reorganize will only take care of the existing fragmentation |
23 | SQL 2000 use DBCC DBREINDEX | SQL 2000 use DBCC INDEXDEFRAG |
24 | Even If the index spans multiple data files, it will be taken care | If the index spans multiple data files, reorganizing will only reorder pages within the same file, it won’t move pages between files |
http://technet.microsoft.com/en-us/library/ms189858.aspx
http://colleenmorrow.com/2011/08/22/index-maintenance-rebuild-vs-reorg/
Index should be rebuild when index fragmentation is great than 40%
ReplyDeleteBut Microsoft recommends 30%.
Nice Article
ReplyDeleteNice post to understand Reorganise and rebuild.. well done.
ReplyDeleteNice Article...! Congratulations
ReplyDeleteSuper!!!!!!!! Bhai nice post
ReplyDeleteGood one, easy to understand for beginners as well. Thanks
ReplyDeleteDepending on my knowledge ,reorg is online process which checks every page ,Rebuild is offline process,Comparing both of these.Reorg will be slower one . Please correct me if there is any error.
ReplyDeleteNice one...easy to understand for beginners
ReplyDeleteThank You