SQL Server: Is Fill Factor applied during INSERT?

The Fill factor setting controls the amount of space that is left available on the index data pages.

The goal is to allow new rows to be inserted or existing ones to change size (UPDATE)  without resulting in a page split.
By default, the server setting is set to “0” which means that the leaf-level pages are filled to their maximum capacity (8060 bytes).

The Fill Factor setting is not applied when data are inserted with INSERT statements.
I discussed this topic recently with a few DBAs and found that it is not a very well-known fact.

It is documented, the Fill Factor is only applied when creating or rebuilding an index.

When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth.

We can confirm Fill Factor is not applied during Inserts with the following demo.

DROP TABLE IF EXISTS DemoFF;

CREATE TABLE DemoFF (
	id int identity not null
	, col varchar(50)
);

CREATE CLUSTERED INDEX CX_DemoFF_id
	ON DemoFF(id)
	WITH(FILLFACTOR=60);
go

I created a DemoFF table with a clustered index that uses the Fill Factor setting with a value of 60%.
The index data pages should be at most 60% full.

Now let’s insert some data. This is 5000 rows.

INSERT INTO DemoFF (col)
	VALUES (replicate('c', 50));
go 5000

Using the sys.dm_db_index_physical_stats DMV we can see the average amount of space available on each page.

SELECT index_type_desc, alloc_unit_type_desc, index_depth
	, ROUND(avg_page_space_used_in_percent, 2) AS avg_page_space_used_in_percent
    , page_count, record_count, avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(
    DB_ID('AdventureWorks')
    , OBJECT_ID('DemoFF'), NULL, NULL , 'DETAILED')
WHERE index_level = 0 -- 0 for index leaf levels

After inserting 5000 rows the pages are 99% full.

Now let’s rebuild the index and check again the space used at the index leaf level.

ALTER INDEX CX_DemoFF_id ON DemoFF REBUILD;

This short post demonstrates that Fill Factor is only applied when an index is created or rebuilt. The free space will then be filled when new rows are inserted or existing rows increase in size.
Applying fill factors can reduce the effect of fragmentation but will increase space usage both on disk and in the buffer pool.
The configuration of a FIll Factor for example at 80% on any new installation of SQL Server is often debated. I recommend staying on the default configuration (no FIll Factor) and making a change only if it fixes a clearly identified problem. This will certainly be the subject of another post.