Why do we need to rebuild and reorganize Indexes in SQL Server?
After searching the internet i couldn't find the reason for
- Why do we need to rebuild and reorganize indexes in SQL Server?
- what does internally happens when we rebuild and reorganize?
An article on a site says : The index should be rebuild when index fragmentation is great than 40%. The index should be reorganized when index fragmentation is between 10% to 40%. The 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. The ONLINE option will keep the 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?
As you perform inserts updates and deletes, your indexes will become fragmented both internally and externally which will initially help you solve SQL server reorganize the index. Internal fragmentation is you have a high percentage of free space on your index pages, meaning that SQL Server requires you to read more pages when scanning the index. External fragmentation is when the pages of the index are not in order anymore, 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 altogether, meaning virtually all queries would have to perform a table scan or clustered index scan. This can damage your performance a lot, so be careful while doing this!
When you reorganize an index, then SQL Server uses the existing index pages and just shuffles data around on those pages. This will alleviate 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 to the data of the index and uses a new set of index pages. This will obviously alleviate 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 parallelize 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 to solve SQL server reorganize the index. This is a trade-off between the best fragmentation levels and the time is taken to rebuild the index. For near 0 fragmentation, use MAXDOP 1 and sort the results in TempDB.