Wednesday, 4 January 2012

Index Rebuild Vs Index Recostructs

There are 2 options

1.    Index Rebuild : This process drops the existing Index and Recreates the index.

2.    Index Reorganize : This process physically reorganizes the leaf nodes of the index.

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.



1 comment: