In SQL Server, the general rule of thumb is:

  • If index fragmentation is less than 5%, no action is necessary.
  • If index fragmentation is between 5% and 30%, reorganizing the index is usually sufficient.
  • If index fragmentation is over 30%, it’s often best to rebuild the index.
-- Reorganize indexes
ALTER INDEX IX_OrderShipments_OrderId ON OrderShipments REORGANIZE;

-- Rebuild indexes
ALTER INDEX PK_OrderShipments ON OrderShipments REBUILD;

Remember to run these commands during off-peak hours, as they can be resource-intensive.