Index Rebuild versus Reorganize: What’s the difference?

In a nutshell, you should rebuild when an index is over 30% fragmented and reorganize when an index is between 10% and 30% fragmented. If fragmentation is below 10%, you can probably just leave that index alone. Rebuilding takes more server resources (and uses locks unless you use the ONLINE option available in 2005 Enterprise and Development editions), so reorg when possible. Syntax below to rebuild or reorg all indexes on a table named tTable follows…

REBUILD

ALTER INDEX ALL ON dbo.tTable REBUILD
GO

REORGANIZE

ALTER INDEX ALL ON dbo.tTable REORGANIZE
GO
About these ads
Tagged , , , , , , , , , , , , ,

7 thoughts on “Index Rebuild versus Reorganize: What’s the difference?

  1. Anonymous says:

    In SQL Server 2005, one can choose to either rebuild the index or re-organize the index. There are differences in what these commands do and it is important to understand those differences. At a very simple level, here is how you would re-organize or rebuild an index:

    CREATE TABLE DECIPHER_DATA (COL1 INT, COL2 NVARCHAR(10));
    CREATE UNIQUE INDEX DECIPHER_DATA_IND_1 ON DECIPHER_DATA (COL2);

    ALTER INDEX DECIPHER_DATA_IND_1 ON DECIPHER_DATA REORGANIZE;
    ALTER INDEX DECIPHER_DATA_IND_1 ON DECIPHER_DATA REBUILD;

    Here are the differences between the two:

    1) Index rebuild works by re-creating the index internally again and when that has been achieved, it drops the existing index where as index reorganize is the process of physically re-organizing the leaf nodes of the index.

    2) During the index rebuild process, the statistics are also re-computed – same as when a new index gets created. Reorganize on the other hand does not update the statistics. Reorganize essentially just swaps one page with another and thus does not require free space for this operation like rebuild does. Infact, reorganize can free up some pages as it does the reorg in two phases – compaction and defrag. A reorganize can remove almost all of the logical fragmentation but it cannot necessarily fix extent fragmentation in which the previous and the next extents are physically contiguous.

    3) Another point to note is that an 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). The ALTER INDEX…REORGANIZE command shown above is the same as DBCC INDEXDEFRAG but there is one difference. ALTER INDEX…REORGANIZE has some additional features like large objects compaction (LOB_COMPACTION). And this is an online operation.

    4) Regarding partitions of an index, if an index has multiple partitions, then you cannot rebuild a single partition online. You can reorganize a single index partition online. If you want to rebuild an index with multiple partitions in an online environment, you need to rebuild the entire index which means rebuilding all the partitions.

    So, how frequently should one do the rebuild/reorganize? Like many answers in the IT field, it depends :-) It depends on the fillfactor, it depends upon the amount of the data that is changed between the rebuild/reorganize operations and it depends upon what logical fragmentation value you consider to be the threshold for forcing these operations.

    An additional question that was raised by one of our colleagues was whether the statistics on non-indexed columns also get re-computed when a rebuild is done? He was talking about the auto create statistics (the ones that you would have seen with the names like _WA_sys_xxxx) or the ones that are explicitly created by using the create statistics command. If we are rebuilding an index, does it make sense to also rebuild those at the same time especially if there is any co-relation between them? Does that happen automatically upon a rebuild? The answer is no. It cannot happen automatically since the co-relation is not stored anywhere and those statistics are stored separately from those indexes. In SQL Server 2008, there is a DATE_CORRELATION_OPTIMIZATION database SET option which can help improve the performance of those queries in which 2 tables are in an inner join condition and whose date/datetime data-type columns are co-related example: PO_HDR might have ORDER_DATE and PO_DTL might have PACK_DATE, SHIP_DATE, DUE_DATE etc.. I will check to see whether a rebuild in that case forces the re-build on the co-related index as well and if no index exists, whether the stats are re-computed on those co-related columns if this option is on. Will post our results here once we are done with my tests

  2. Anonymous says:

    Really great explanation, i was looking for in depth explanation , have tried lots but couldnt find it anywhere.

    Thanks for your help

  3. Anonymous says:

    If an index is heavily fragmented (over 50 percent) will the alter index reorganize reduce the level of the fragmentation?

    Thanks

    Mark

  4. […] and a SQL DBA’s perspective on rebuilding versus reorganizing indexes at http://realworlddba.wordpress.com/2008/02/04/index-rebuild-versus-reorganize-whats-the-difference/. I’m thinking that one option could be reorganizing indexes before the differential backups […]

  5. […] index and level of defragmentation I found this question here but no answer yet: If an index is heavily fragmented (over 50 percent) will the alter index […]

  6. Daniel says:

    You may also have to consider your monthly budget to see what your borrowing experience will be
    like. This will give you a detailed description of the best
    cars in federal debt relief present market.
    By doing window shopping you find that there are laws in place in Washington state that are designed to protect consumers.
    There must be opportunity to save some extra money.

  7. anchor says:

    Today, many USA companies offer Debt Relief Services programs to help recruit and retain employees.
    In some cases your account may be sold to pay back their loans.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: