SQL Server Interview Question #225

What is fill factor?

Advanced Indexing Senior Advanced

Detailed Explanation

Fill factor controls how full SQL Server makes leaf-level index pages when an index is created or rebuilt. A fill factor below 100 leaves free space that can accommodate future inserts or row expansion.

A lower fill factor may reduce page splits in some workloads but increases index size, memory consumption, and I/O because fewer rows fit on each page.

Fill factor should therefore be selected from measured modification patterns. Setting a low value globally is usually not a good tuning strategy.

Code Example

CREATE INDEX IX_Orders_OrderDate
ON dbo.Orders(OrderDate)
WITH (FILLFACTOR = 90);