What is the FILL FACTOR ?

A FILL FACTOR is one of the important arguments that can be used while creating an index.

FILL FACTOR specifies a value interns of the percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild. Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage. By setting the fill-factor value, you specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth.

Specifying a fill-factor value of 70 would imply that 30 percent of each page will be left empty, providing space for index expansion as data is added to the underlying table. The fill-factor setting applies only when the index is created or rebuilt

By Default fill factor is 0 of the database, even you can set it out at SQL Server instance level using instance Properties in below screen .

The following example shows how to use sp_configure to set the fill factor server configuration option to 100. Using sp_configure, you can change fill factor only when show advanced options is set to 1. The setting takes effect after restarting the server.

Use ABC;

GO

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'fill factor', 100;

GO

RECONFIGURE;

GO