If you’ve gone through the steps for changing the default fill factor in SQL Server, but it doesn’t seem to be taking effect on newly created indexes, it could be because you’ve missed one or two crucial steps.

This issue can also be seen by comparing the value and value_in_use columns when querying the sys.configuration view or using sp_configure to view the current setting.

Steps to Changing the Default Fill Factor

Just to be clear, when I say “default fill factor”, I’m referring to the fill factor configuration option. SQL Server sets this to 0 by default, but we can change this value if required.

Changing the default fill factor requires the following steps:

1. Enable show advanced options
2. Change the fill factor Setting
3. Run RECONFIGURE
4. Restart the Server

Notice the last two steps. First, we need to run RECONFIGURE to install the change. We then need to restart the server before the change is completely done.

If we don’t do these two steps, we’ll see a different in the value and value_in_use columns when querying the sys.configuration view or using sp_configure to view the current setting.

Here’s what that might look like:

SELECT
    name,
    value,
    value_in_use
FROM sys.configurations
WHERE name LIKE 'fill%';

Example result:

name             value  value_in_use
---------------  -----  ------------
fill factor (%)  90     0           

In this case, it appears that someone has either gone through steps 1 thru 2 when changing the fill factor configuration option, or they’ve managed to get to step 3. Either way, they haven’t gone all the way up to step 4.

In this case, any new indexes that are created will continue to have a fill factor of 0 (unless their fill factor is explicitly set to a different value).

Once RECONFIGURE has been run and a server restart has taken place, the query should return the following:

SELECT
    name,
    value,
    value_in_use
FROM sys.configurations
WHERE name LIKE 'fill%';

Example result:

name             value  value_in_use
---------------  -----  ------------
fill factor (%)  90     90          

From that point on, any new indexes that haven’t had their explicitly fill factor specified will automatically be set to 90.

See How to Change SQL Server’s Default Fillfactor for a step-by-step guide for changing this option.