Reorgainize and Rebuild help to fix index fragmentagion. Fragmentation for indexes occurs for different reasons suc as DML operatons (INSERT, UPDATE, and DELETE statements) against the table and DDL operations like shrinking a database.
A system table valued function sys.dm_db_index_physical_stats can be used to see how fragmented an index is.
SELECT Db_name(database_id) dbName, Object_name(s.object_id) tableName, i.name indexName, index_type_desc, avg_fragmentation_in_percent, page_count FROM sys.Dm_db_index_physical_stats (Db_id(N'ContosoRetailDW'), Object_id(N'dbo.FactSales'), NULL, NULL, 'DETAILED') s JOIN sys.indexes AS i ON s.object_id = i.object_id AND s.index_id = i.index_id;
The main difference between rebuilding an index and reorganizing is a rebuild can be executed online or offline where are reorgainize an index is always executed online.
If avg_fragmentation_in_percent is greater than 5% and less than or equal to 30% we could use REORGANIZE to correct the fragmentaion where as if the fragmentation is greater than 30% we should use REBUILD.
To reorganize our table we could use
ALTER INDEX [indexName] ON [tableName] REORGANIZE; GO
To rebuild our table we could use
ALTER INDEX [indexName] ON [tableName] REBUILD; GO
For example to reorgainize PK_FactSalesQuota_SalesQuotaKey Index in ContosoRetailDW.dbo.FactSalesQuota
ALTER INDEX PK_FactSalesQuota_SalesQuotaKey ON ContosoRetailDW.dbo.FactSalesQuota REORGANIZE; GO
and To rebuild the same index we could use
ALTER INDEX PK_FactSalesQuota_SalesQuotaKey ON ContosoRetailDW.dbo.FactSalesQuota REBUILD; GO