A SQL Server index setting that specifies what percentage of each index leaf page should be filled with data during an index build or rebuild, reserving space for future row insertions.
- A fill factor of 80 means 80% of each page is filled during the rebuild: This leaves 20% free for new inserts and updates before page splits occur.
- Lower fill factors reduce page split frequency: They are better suited for high-insert workloads but result in larger index sizes and increased I/O for read operations.
- Higher fill factors (90–100%) are appropriate for read-heavy or append-only workloads: These workloads experience few row insertions, or inserts always occur in key order.
- The default fill factor of 0 (treated as 100%) is appropriate only for clustered indexes on monotonically increasing keys: Examples include
IDENTITYcolumns where inserts always occur at the end of the index. - Fill factor is only applied during index creation or rebuild: It is not maintained dynamically, and pages gradually fill up over time until the next rebuild resets the fill factor.
- DBAs tune fill factor in conjunction with index rebuild schedules: A higher fragmentation tolerance may allow less frequent rebuilds while using a lower fill factor.
- Related terms: Page Split, Index Rebuild, Fragmentation, Bloat, Index Maintenance.
