SQL SERVER – Difference Between Index Rebuild and Index Reorganize Explained with T-SQL Script

Index Rebuild : This process drops the existing Index and Recreates the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO

Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO

Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

Reference : Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
,
Previous Post
SQL SERVER – Enabling Clustered and Non-Clustered Indexes – Interesting Fact
Next Post
SQL SERVER – 2008 – New DataTypes DATE and TIME

Related Posts

101 Comments. Leave new

  • So in short: the “rebuild” rebuilds the index and “reorganize” reorganizes the index. Thanx.

    Reply
  • Hi Dave, love your stuff, but it would be helpful if you also explained why its recommended to just rebuild if over 40% fragmented – I know its faster and the index is smaller, but it locks all the tables, whereas reorganise does not. SO reorganise is online, a big win in some situations, no table locking being a priority sometimes.
    regards
    Merlin

    Reply
  • Hi Pinal,

    Keep sharing the Information and it will help to both Freshers and Even Experienced DBA’s .God Bless You.

    Reply
  • now only i referred the index concepts. which is very useful to me. Thank you…

    Reply
  • Hi pinal,

    ALTER INDEX | ALL ON { REBUILD | REORGANIZE } ;

    THEN what is the main use

    DBCC INDEXDEFRAG (,) ;

    Reply
  • Hi Pinal,

    My project environment is on AlwaysOn 2012 sql server, I am planning to setup index rebuild and reorganize maintenance jobs. If i setup index rebuild maintenance job, does it only work with fragmentation greater than 30 – 40% or all indexes? How can i set index rebuild only for fragmentation > 30 % ?

    Please suggest me

    Reply
  • Pinal,

    We all appreciate this blog and how super useful it is man, You have saved me countless times and helped me understand DB stuff, I am a Software Engineer but I do a whole bunch of DBA related work for both my company and it’s clients. and you my friend…has helped me A LOT.

    I just wanted to leave this appreciative comment, Keep up to good work man, there is many people that appreciate it.

    Reply
  • We are experiencing the performance issues in production region(sql server 2008 r2) when we moved from rebuild job(full re-index offline ) to reorganize job (smart index).
    In rebuild job(full re-index offline ) some of the queries are good performance(query(This will run non business hours) execution time taken less than 1.5 minute)

    But after implementing the reorganize job from rebuild job same query has been taken(most of the days) more than 100 minutes.

    Below are the criteria for reorganize job:

    Indexes with fewer than 1000 pages are not rebuilt or reorganized (too small)
    Indexes with less than 5% are not rebuilt or reorganized
    Indexes with fragmentation between 5% and 30% are reorganized
    Indexes with fragmentation greater than 30% are rebuilt online. If the index does not support rebuilding online then the index is reorganized. Highly fragmented indexes can also be rebuilt offline (optional).

    ****Could you please let me know why some of the queries are getting very less performance(taking more execution time) when moved to reorganize(smart index) job from rebuild (full re-index off line ) job. its real production burning issue. Please help me (i am not a DBA) .Thank you.

    Below is example
    one of the query is having multiple joins and having table row count in production region as mentioned below :
    Tables Row Count
    A 11244885
    B 8763368
    C 4956552
    D 4956407
    E 3678131
    F 1231529

    Reply

Leave a Reply

Menu