Index Rebuild : This process drops the existing Index and Recreates the index.
Index Reorganize : This process physically reorganizes the leaf nodes of the index.
Recommendation: 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.
USE AdventureWorks;GOALTER INDEX ALL ON Production.Product REBUILD
GOIndex Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks;GOALTER INDEX ALL ON Production.Product REORGANIZE
GORecommendation: 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.
No comments:
Post a Comment