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
Hi Pinal,
Keep sharing the Information and it will help to both Freshers and Even Experienced DBA’s .God Bless You.
Thanks Matish. I have been doing that from more than 6 years – every day.
now only i referred the index concepts. which is very useful to me. Thank you…
Hi pinal,
ALTER INDEX | ALL ON { REBUILD | REORGANIZE } ;
THEN what is the main use
DBCC INDEXDEFRAG (,) ;
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
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.
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