locked
Why not always rebuild indexes? RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi!

    I understand the need for reorganizing or rebuilding indexes, and I also understand that reorganize only reorganizes the indexes, whereas rebuild rebuilts the indexes from scratch, thus completely removing fragmentation.

    However, the question is, why not just always rebuild the indexes?

    For example, is it incorrect to create a maintenance plan that will periodically (every night, for example) rebuild all indexes on all tables?

    Thanks!

    Tuesday, October 18, 2011 8:56 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    http://technet.microsoft.com/en-us/library/ms189858.aspx 

    http://sqldbpool.com/2009/12/12/index-rebuild-vs-index-reorganize-diffrence-beweent-index-rebuild-and-index-reorganize/

     

    vt

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    • Edited by SimpleSQL Tuesday, October 18, 2011 9:06 PM
    • Proposed as answer by Peja Tao Wednesday, October 19, 2011 3:33 AM
    • Marked as answer by Mang AlexMVP Wednesday, October 19, 2011 7:49 AM
    Tuesday, October 18, 2011 9:03 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Have a look

    For sql server 2008/2005 http://www.sqlserverblogforum.com/2010/11/index-defragmentation-script/

    For sql server 2000 http://www.sqlserverblogforum.com/2011/03/index-defragmentation-script-for-sql-server-2000/

    Conditions to rebuild and reorganize the indexes as per Rule of thumb.

    1. Fragmentation >=30 AND PAGES>1000 then rebuild
    2. Fragmentation between 15 to 29 AND PAGES>1000 then reorganize & update statistics
    3. If the page level locking is disabled then rebuild
    4. If the above conditions are false then update the statistics


    Muthukkumaran Kaliyamoorthy

    Helping SQL DBAs and Developers >>> SqlserverBlogForum
    • Marked as answer by Mang AlexMVP Wednesday, October 19, 2011 7:49 AM
    Wednesday, October 19, 2011 7:29 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Index Rebuild Index Reorganize
    It is offline operation It is online operation
    Option is available in all SQL Server 2005 edition Option is available in SQL Server 2005 Enterprise and Developer edition only
    Index rebuilds works by re-creating the index internally again and when that has been achieved, it drops the existing index. Index reorganize is the process of physically re-organizing the leaf nodes of the index
    Index rebuild need more log space so it is advisable to change the database recovery model Simple or Bulk-logged Reorganize swaps one page with another and thus does not require free space for this operation like rebuild does. Infect, reorganize can free up some pages as it does the reorganize 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.
    During the index rebuild process, It will also re-compute index statistics Reorganize on the other hand does not update the statistics
    Can be done on any data type column because it is offline operation. 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).
    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. 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
    USE AdventureWorks;
    GO
    ALTER INDEX ALL ON Production.Product REBUILD
    GO    		 USE AdventureWorks;
    GO
    ALTER INDEX ALL ON Production.Product REORGANIZE
    Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%
    ONLINE option will not keep index available during the rebuilding. ONLINE option will keep index available during the rebuilding.

     

     

    I have having to investigate on forums to find an answer, so here it is, as suggested by v.vt, from the second link in his post.

    • Marked as answer by Mang AlexMVP Wednesday, October 19, 2011 7:49 AM
    Wednesday, October 19, 2011 7:49 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

    http://technet.microsoft.com/en-us/library/ms189858.aspx 

    http://sqldbpool.com/2009/12/12/index-rebuild-vs-index-reorganize-diffrence-beweent-index-rebuild-and-index-reorganize/

     

    vt

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker
    • Edited by SimpleSQL Tuesday, October 18, 2011 9:06 PM
    • Proposed as answer by Peja Tao Wednesday, October 19, 2011 3:33 AM
    • Marked as answer by Mang AlexMVP Wednesday, October 19, 2011 7:49 AM
    Tuesday, October 18, 2011 9:03 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Have a look

    For sql server 2008/2005 http://www.sqlserverblogforum.com/2010/11/index-defragmentation-script/

    For sql server 2000 http://www.sqlserverblogforum.com/2011/03/index-defragmentation-script-for-sql-server-2000/

    Conditions to rebuild and reorganize the indexes as per Rule of thumb.

    1. Fragmentation >=30 AND PAGES>1000 then rebuild
    2. Fragmentation between 15 to 29 AND PAGES>1000 then reorganize & update statistics
    3. If the page level locking is disabled then rebuild
    4. If the above conditions are false then update the statistics


    Muthukkumaran Kaliyamoorthy

    Helping SQL DBAs and Developers >>> SqlserverBlogForum
    • Marked as answer by Mang AlexMVP Wednesday, October 19, 2011 7:49 AM
    Wednesday, October 19, 2011 7:29 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Index Rebuild Index Reorganize
    It is offline operation It is online operation
    Option is available in all SQL Server 2005 edition Option is available in SQL Server 2005 Enterprise and Developer edition only
    Index rebuilds works by re-creating the index internally again and when that has been achieved, it drops the existing index. Index reorganize is the process of physically re-organizing the leaf nodes of the index
    Index rebuild need more log space so it is advisable to change the database recovery model Simple or Bulk-logged Reorganize swaps one page with another and thus does not require free space for this operation like rebuild does. Infect, reorganize can free up some pages as it does the reorganize 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.
    During the index rebuild process, It will also re-compute index statistics Reorganize on the other hand does not update the statistics
    Can be done on any data type column because it is offline operation. 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).
    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. 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
    USE AdventureWorks;
    GO
    ALTER INDEX ALL ON Production.Product REBUILD
    GO    		 USE AdventureWorks;
    GO
    ALTER INDEX ALL ON Production.Product REORGANIZE
    Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%
    ONLINE option will not keep index available during the rebuilding. ONLINE option will keep index available during the rebuilding.

     

     

    I have having to investigate on forums to find an answer, so here it is, as suggested by v.vt, from the second link in his post.

    • Marked as answer by Mang AlexMVP Wednesday, October 19, 2011 7:49 AM
    Wednesday, October 19, 2011 7:49 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    if you use a FG, as much as i understand it dosen't solve the defrag problem, beacuse 2 FG files can intersect in the defrag level and will be "mixed" and the same place on disk.

    is there a way to make a FG file, to be continuous? so only when 1 FG end's the second one starts?

    Wednesday, October 19, 2011 10:09 AM