Index Rebuild

An index is a database object that provides access to data in the columns of a table based on the values within them. They speed up the querying process, but indexes typically become fragmented over time, and highly fragmented indexes result in slow query performance and can cause your application to respond slowly.

An index rebuild creates a brand new structure for the index. The structure of the rebuilt index can be the same or quite different from your prior index.

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. Note you can change the fill factor when rebuilding an index.

Reorganizing vs Rebuilding

Reorganizing is a more lightweight “index repair” option. A reorganization involves the leaf level of the index, and is designed to repair the physical ordering of pages. The process also compacts pages to apply the previously set fill factor. Reorganizing can be performed when your app is online, and if you cancel it then it can just stop where it is and you can rollback relatively easily.

Most database experts suggest rebuilding an index if fragmentation has reached 40%. The general recommendation is to consider reorganizing an index if fragmentation is between 10% and 40%. Note that the index rebuilding process is relatively resource intensive, and in most cases, it also locks the database resources.