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)
101 Comments. Leave new
Hey…U r rocking man..
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.
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?
in above post text got truncated…
avg_fragmentation_in_percent >5 and 30 (rebuild)
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
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.
Thanks Pinal, you are in the right place and the right moment.
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!
OMG – now it is clear – i got lock from the transaction and not from the index reorganize!
Good morning!
Love your work Pinal Dave – I am always dropping by your blog. Very very useful. You’re a lifesaver!!!
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
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?
I wanted to know the difference between them… Internally what will happen when reorganizing and rebuilding the indexes
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 ?
Hi Pinal,
how do i know the Fragmentation Percentage level?
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.
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…
Thanks for your simplest answer
So in short: the “rebuild” rebuilds the index and “reorganize” reorganizes the index. Thanx.
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