SQL Reference - FILLFACTOR

FILLFACTOR

FILLFACTOR is a SQL Server configuration setting that determines the amount of free space left on a page in percent after data is inserted during the index creation or reorganization. Beneficial in reducing page splits by modifying the density of the index pages, it directly influences the index's performance and maintenance needs. The FILLFACTOR value ranges from 1 to 100; a higher value leads to less empty space and effectively a denser index, while a lower value leads to more empty space and potentially less page splits.

Example

CREATE TABLE test_table(
    ID Int PRIMARY KEY,
    Data Text
) WITH (FILLFACTOR = 70);

INSERT INTO test_table(ID, Data) VALUES
(1, 'Data 1'),
(2, 'Data 2'),
(3, 'Data 3'),
(4, 'Data 4'),
(5, 'Data 5');

SHOW (pg_total_relation_size('test_table'));

Output

pg_total_relation_size
-----------------------
65536

Explanation

In the above example, a table test_table is created with two columns ID and Data. By specifying WITH (FILLFACTOR = 70), the table is set to leave 30% of each block empty for future updates and inserts under the same database block, resulting in less page splits. Five rows of data are inserted into the table. The final command SHOW (pg_total_relation_size('test_table')) shows the total disk space occupied by the test_table table in bytes.

Example

CREATE CLUSTERED INDEX CI_Employees_Name
    ON Employees (Name)
    WITH (FILLFACTOR = 80);

Output

Command(s) completed successfully.

Explanation

The FILLFACTOR option determines the percentage of space on each leaf-level page that is to be filled with data, reserving the rest as a free space for future growth. In this example, a clustered index is created on the “Employees” table on the “Name” column with a fill factor value of 80, meaning each page in this index will be filled with 80% data, reserving 20% for future data growth.

Example

CREATE TABLE employees_allocation (
    employee_id NUMBER(4) NOT NULL,
    first_name VARCHAR2(20),
    last_name VARCHAR2(20),
    start_date DATE,
    end_date DATE,
    job_id NUMBER(6),
    city_id NUMBER(4)
)
STORAGE (
    INITIAL 8K
    NEXT 4K
    MINEXTENTS 2
    MAXEXTENTS 50
    PCTINCREASE 0
)
PCTFREE 0
PCTUSED 99
FILLFACTOR 70;

Output

Table EMPLOYEES_ALLOCATION created.

Explanation

The FILLFACTOR is used to leave a certain percentage of each data block empty so that when new rows are inserted, they can be placed on the same data block(s). In this case, FILLFACTOR is set to 70 which means that 70% of each data block will be filled with data, leaving 30% of each data block free to accommodate future data.