Fillfactor is an option that we can set on SQL Server indexes in order to fine tune index data storage and performance. It 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.

The default fillfactor can be set as a configuration option. SQL Server sets this value to 0, and so this is what’s used when we create an index without specifying a fillfactor. The value of 0 is actually the equivalent of 100 (or meaning 100%). In other words, by default, the leaf-level pages are filled to capacity.

But we can change this to another value if required. We can do this by changing the fill factor configuration option.

Note that this requires a server restart, so you should plan this carefully if changing it in a production environment.

Change the fill factor Configuration Option

There’s not much code required to change the fill factor configuration option. However, fill factor is considered an advanced configuration option, which means that there’s an extra step involved when compared to normal “less-advanced” options.

Step 1: Enable show advanced options

First of all, we need to enable the show advanced options configuration option. By default, this configuration option is set to 0, which means that it’s off, or disabled. If we try to access the fill factor configuration option (or any other advanced option) while this is disabled, we’ll get an error.

But we can enable show advanced options by setting it to 1:

EXEC sp_configure 'show advanced options', '1';
RECONFIGURE;

Output:

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

Note that we also ran RECONFIGURE straight after the sp_configure part. This is a required part of enabling advanced configuration options. We can see that the output message tells us to run RECONFIGURE too, but we already did that, so we can ignore that part of the message.

Anyway, now that we’ve enabled show advanced options, we now have access to the advanced configuration options, and more importantly for our purposes, the fill factor option.

Step 2: Check Existing fill factor Setting

Let’s check our current fill factor setting:

EXEC sp_configure @configname = 'fill factor';

Output:

name             minimum  maximum  config_value  run_value
---------------  -------  -------  ------------  ---------
fill factor (%)  0        100      0             0        

We can see that the current value for the fill factor configuration option is 0, which is what it would be if it had never been changed from the SQL Server default.

Step 3: Change the fill factor Setting

Now’s the moment we’ve all been waiting for…

Let’s change fill factor to another value:

EXEC sp_configure 'fill factor', 90;
RECONFIGURE;

Output:

Configuration option 'fill factor (%)' changed from 0 to 90. Run the RECONFIGURE statement to install.

The option has been changed.

So the server-wide default fillfactor is now 90, which means that 10 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table.

Also, just to be clear, we can see that the output message mentions RECONFIGURE again, but we already included that in the code, so the change is (almost) complete.

Let’s check the default fill factor again:

EXEC sp_configure @configname = 'fill factor';

Output:

name             minimum  maximum  config_value  run_value
---------------  -------  -------  ------------  ---------
fill factor (%)  0        100      90            0        

We can see that it is now 90, as specified. However, the run_value is still 0. This needs to be updated to the new value, which will require a server restart.

Step 4: Restart the Server

Restart the server in order to effect the change completely.

Once we’ve done that, the run_value column will be updated:

EXEC sp_configure @configname = 'fill factor';

Output:

name             minimum  maximum  config_value  run_value
---------------  -------  -------  ------------  ---------
fill factor (%)  0        100      90            90      

We can now see that the run_value has been updated in accordance with our changes.

Step 5: Hide Advanced Options

It’s advisable to set show advanced options back to zero once you’re done with the advanced options:

EXEC sp_configure 'show advanced options', '0';
RECONFIGURE;

Output:

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

Now the advanced configuration options will be hidden once again.