SQL SERVER - 2005 - Find Index Fragmentation Details - Slow Index Performance

The following document was found in www.sqlauthority.com and was extremely useful.

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

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.

Reference : Pinal Dave (http://www.SQLAuthority.com)