Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

After searching the internet i couldn't find the reason for

  1. Why do we need to rebuild and reorganize indexes in SQL Server ?

  2. what does internally happens when we rebuild and reorganize ?

An article on a site says :

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.

I couldn't understand this, though it says that WHEN to do this, but I would like to know WHY do we need to rebuild and reorganize indexes ?

share|improve this question

migrated from Nov 9 '12 at 15:22

This question came from our site for professional and enthusiast programmers.

This is the question about why, and here is the question about when. – Nick Chammas Jul 29 '13 at 8:21
up vote 16 down vote accepted

As you perform inserts updates and deletes, your indexes will become fragmented both internally and externally.

Internal fragmentation is you have a high percentage of free space on your index pages, meaning that SQL Server needs to read more pages when scanning the index.

External fragmentation is when the pages of the index are not in order any more, so SQL Server has to do more work, especially in IO terms to read the index.

If your indexes become too fragmented, at best, your queries will be less efficient but at worst, SQL Server will just stop using the indexes all together, meaning virtually all queries would have to perform a table scan or clustered index scan. This will hurt your performance a lot!

When you reorganise an index, then SQL Server uses the existing index pages and just shuffels data around on those ages. This will elliviate internal fragmentation and can also remove a small amount of external fragmentation. It is a lighter weight operation than rebuild and is always online.

When you rebuild an index, SQL Server actually resorts the data of the index and uses a new set of index pages. This will obviously elliviate both internal and external fragmentation but is a more heavy weight operation and by default causes the index to go offline, although it can be performed as an online operation, depending on your SQL Server version and settings.

Please do not expect to have 0 fragmentation after a Rebuild however. Unless you use a MAXDOP query hint, SQL Server will parallelise the rebuild operation and the more processors involved, the more fragmentation there is likely to be, because each processor or core, will rebuild their section or fragment of the index individually, without regard for each other. This is a trade off between best fragmentation levels and time taken to rebuild the index. For near 0 fragmentation, use MAXDOP 1 and sort the results in TempDB

share|improve this answer
thank you for your prompt reply, now i understood when to do rebuild and reorganize indexes. also it would better to ReBuild only when the traffic is less or no traffic at all on our website. – Mourya Nov 9 '12 at 7:35
Yes, that is correct. You can actually use server agent to respond to low CPU usage on the server by starting a job. This job could contain your index rebuild scripts. Also, if you are in a 24/7 environment, I suggest you write a script that only reorganises or rebuilds the indexes that actually require it, rather than rebuilding all. – Pete Carter Nov 9 '12 at 7:51
As a side question, any idea why SQL doesn't reorganise indexes in the background during low demand periods (i.e. to avoid requiring scheduled tasks or manually requesting this action)? – JohnLBevan Apr 6 '13 at 17:40

As described in previous answers - After indexes are created, they will undergo automatic maintenance by the SQL Server Database Engine whenever insert, update or delete operations are executed on the underlying data.

Even so, these automatic modifications will continuously scatter the information in the index throughout the database – fragmenting the index over time. As a result – indexes now have pages where logical ordering (based on the key value) differs from the physical ordering inside the data file. This means that there is a high percentage of free space on the index pages, and that SQL Server has to read higher number of pages when scanning each index.

As described in this article, the solution to fragmented indexes is to rebuild or reorganize indexes.

There is a generally accepted solution based on the percent of fragmentation:

  • Fragmentation is less than 10% – no de-fragmentation is required. It is generally accepted that in majority of environments index fragmentation less than 10% in negligible and its performance impact on the SQL Server is minimal

  • Fragmentation is between 10-30% – it is suggested to perform index reorganization

  • Fragmentation is higher than 30% – it is suggested to perform index rebuild

Hope I helped.

share|improve this answer
Doesn't seem to add anything to the 3+ year old accepted answer. Any affiliation to the site you link to? – AakashM yesterday

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.