Why it is necessary to rebuild and reorganize Indexes in SQL Server?
Even after googling alot 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 :
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 sql server reorganize indexes?
Rebuilding an index means deleting the old index replacing it with a new index. Performing an index rebuild eliminates fragmentation, compacts the pages based on the existing fill factor setting to reclaim storage space, and also reorders the index rows into contiguous pages. As you start to do inserts, index performance will actually improve for a time as the free-space pages are used, and then start to deteriorate as index fragmentation begins. Eventually, the fragmentation in your index will be worse than it was after you completed your index rebuild, and performance can only get worse.
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. 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. When Need to reorganize index:
Analyze detection results if-
- Fragmentation is less than 10% – no de-fragmentation is required.
- Fragmentation is between 10-30% – it is suggested to perform index reorganization.
- Fragmentation is higher than 30% – it is suggested to perform index rebuild.