DBAtlas · Database operations atlas

Index rebuild / reorganize

SQL ServerMaintenance

Command

-- Rebuild (high fragmentation > 30%). ONLINE requires Enterprise:
ALTER INDEX IX_Customer_Email ON dbo.Customer
  REBUILD WITH (ONLINE = ON, MAXDOP = 4, SORT_IN_TEMPDB = ON);

-- All indexes of a table:
ALTER INDEX ALL ON dbo.Customer REBUILD WITH (FILLFACTOR = 90);

-- Reorganize (moderate fragmentation 5-30%, always online, can be stopped):
ALTER INDEX IX_Customer_Email ON dbo.Customer REORGANIZE;

What it does

REBUILD recreates the index from scratch (removes fragmentation completely, refreshes statistics); REORGANIZE compacts the leaf level in place (lightweight, non-blocking).

When to use it

REORGANIZE for 5-30% fragmentation, REBUILD above 30%. During maintenance windows for large tables.

Caution

ONLINE = ON is available only in Enterprise/Developer; use OFF in Standard (the table is locked). REBUILD generates a lot of log and needs space. MAXDOP and SORT_IN_TEMPDB affect resource usage.

Open in DBAtlas →

Related