What is the Difference Between Rebuild and Reorganize indexes in MS SQL Server


Rebuild vs Reorganize 


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


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:

No comments:
Write comments

Please do not enter spam links

Meet US

Services

More Services