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)

,
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

  • Hi Pinal,

    My Full-Text search takes a long time to perform the search operation. The table on which the Full Text is enabled, contains 97 Lac records and every 1 hour 3 to 4k records are imported to this table.

    During the FTS, CPU shows 100% performance on the database server with high CPU consumption of this process: MSFTESQL

    The rebuilt on the full text catalog is performed every night. I also tried to do a reorganize every-night but it did’nt helped me.

    Could you please guide me what should I do in this case and will Rebuild or Reorganize of FTI , boost performance.

    Reply
  • Hey…U r rocking man..

    Reply
  • What benefits do I lose if I rebuild index on 20% fragmentation or reorganize index on 90% fragmentation? Except availability, because I do it at night, so availability is not important for me.

    Reply
  • Hi Pinal,

    I need clarification w.r.t rebuilding or reorganizing indexes. Based on avg_fragmentation_in_percent >5 and 30 (rebuild)

    SELECT B.name as TableName,c.name IndexName,A.OBJECT_ID,avg_fragmentation_in_percent
    FROM SYS.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A
    JOIN SYS.objects B ON A.object_id=B.object_id
    JOIN SYS.indexes C ON B.object_id=C.object_id
    WHERE c.name is not null

    After rebuilding or reorganizing the indexes “avg_fragmentation_in_percent” remains same. This will change after rebuild or reorganizing the indexes or it will remain same?

    Reply
  • in above post text got truncated…
    avg_fragmentation_in_percent >5 and 30 (rebuild)

    Reply
  • Hi Pinal,

    Thanks for the article.

    I reorganize index(composite cluster index) the table(around 200GB).
    After reorganize its size increased 400GB.
    Could you please help me with this.

    Thanks in Advance.
    Any suggestion are welcome

    Reply
  • Sherwin Lester
    June 11, 2012 2:07 pm

    I have a question here.. when rebuilding or reorganizing indexes.. is there a possibility of data loss?

    I’m planning on implementing this on our server but afraid because of the possible loss of data.

    Thanks in advance.

    Reply
  • Thanks Pinal, you are in the right place and the right moment.

    Reply
  • Hello,

    please tell me why i got a lock with REORGANIZE?

    E.g.:

    — conn 1
    begin tran
    alter index idx_nci_orderid on Sales.OrderDetails reorganize

    — conn 2
    select orderid, productid, unitprice, qty, discount from Sales.OrderDetails where orderid = 10248
    — locked

    — conn 1
    commit tran

    — conn 2 – lock has been released

    Why it is said that REORGANIZE would not lock resources?

    Thanks!

    Reply
  • OMG – now it is clear – i got lock from the transaction and not from the index reorganize!

    Good morning!

    Reply
  • Love your work Pinal Dave – I am always dropping by your blog. Very very useful. You’re a lifesaver!!!

    Reply
  • This is my first time to face indexing over table; so I want to know if I create non-clustered index over a table which is having some records; do I need to rebuild the index of the same?

    Thanks in Advance

    Reply
  • I run the “Rebuild Indexes” using Management Studio. After days, finally all the indexes are showing Status “Success”. However, the Progress still indicating as “executing”. The dialog has been there for days….Is this normal? Or should i kill the process?

    Reply
  • I wanted to know the difference between them… Internally what will happen when reorganizing and rebuilding the indexes

    Reply
  • I’m running a rebuild index from management studio plan . The DB is about 700-800 GB and the job run for a couple of days . I’d like to know :
    – how can I check the % of completed job ?
    – how can I stop it if needed ( simply click on the red x on the window )
    – what happens if the rebuild is stopped ?

    Reply
  • Hi Pinal,

    how do i know the Fragmentation Percentage level?

    Reply
  • Is there any difference between recreating an index and rebuilding and index.
    Wherever I searched online I only find difference between rebuilding and re-organising, but I want to know if recreating index and rebuilding index are different?and if yes which is better to perform after we drop indexes on a table.

    Reply
  • Please help me. how this rebuild and reorganize is working. give some example.

    Reply
  • You have mentioned While Rebuilding index, the existing index will get dropped and it will get recreated again. I did Rebuild index for a column and i couldnt find any Drop Index query in my SQL Profiler. Can u plz explain, how it gets dropped and again recreated? In SQL Profiler it has “ALTER INDEX [TESTIDX] ON [dbo].[STUD] REBUILD WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = OFF )” only…

    Reply
  • Thanks for your simplest answer

    Reply

Leave a Reply

Menu