Answer 1)
Index Rebuild: This process drops the existing Index and Recreates the index. USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO
Index Reorganize: This process physically reorganizes the leaf nodes of the index. USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO
You need to check the fragmentation (using DBCC showcontig) for indices to decide what to go for rebuild or reorganize.
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. |