Enhance index efficiency by using the REBUILD index

Rebuilding an index will do nothing, but just drop the current index and recreate it internally, so that all fragmentations go away, statistics are updated, and physical sorting order in data pages are in co-ordination with each other. It compacts data pages, fills them up with a proper Fill Factor, and adds new data pages if needed. All these things will help in faster data retrieval, but at the same time this is a really very resource-centric job and takes a very long time to finish on large tables with millions of rows.

Getting ready

Decide first whether it is really necessary to rebuild an Index. If it is worth rebuilding an index, only then should one go for this option. Otherwise, use the REORGANIZE index, because the REBUILD index can use lots of server resources. If fragmentation is greater than 30 percent, it is good to use the REBUILD index.

Rebuilding an index can be done in two different modes and it is better to decide what mode...