What is fill factor and how to set it?

It determines the percentage of space on each data page in an index that is filled with data during creation, with the remaining space reserved for future growth and updates. You set it by specifying a value (1-100) when creating or rebuilding an index.

purpose:

the fill factor determines how much space on each page of an index or table is initially filled with data.

benefit:

by leaving some space free, you can accommodate future updates and insertions without immediate page splits or row movements, which can improve performance.

default value:

the default fill factor often varies by database system, with some systems defaulting to 100% (fully filled) for tables and 90% for indexes.

range:

fill factor values typically ranges from 1 to 100, with 100 meaning the pages are filled to capacity.

HOW TO SET FILL FACTOR:

the method for setting fill factor can vary depending on the database system, but generally, you can specify it when creating or rebuilding an index or table.

SQL SERVER

you can specify the fill factor when crating or rebuilding an index using the FILL FACTOR option.

for example 

CREATE INDEX IX_MyTable_Column ON MyTable (Column) WITH (FILLFACTOR=80).

.You can also set a default fill factor for the server instance, but its generally recommended to set it at the index level.

when to use fill factor:

frequent updates 

if your table or index experiences frequent updates or insertions, setting a lower fill factor  can help reduce the need for page splits  and improve performances.

data growth

if you anticipate significant data growth, leaving some space free can prevent fragmentation and improve performances.

performance tuning

experiment with different fill factor values to determine the optimal setting for your specific workload and database environment.