Fillfactor is an option that allows us to fine tune index data storage and performance in SQL Server. It determines the percentage of space on each leaf-level page to be filled with data, therefore 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. However, it’s possible to change this value, and so at some stage we might want to check to see what the current value is.

Below are two ways to check the fill factor configuration option.

Option 1: The sys.configurations View

The sys.configurations catalog view returns server-wide configuration options and their values. We can narrow this down to just the options that we’re interested in:

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

Example result:

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

We can see that the value is currently 0, which is the default value for this option.

Note that if the values in the value_in_use and value columns are different, it’s probably because someone changed the fill factor configuration option, but forgot to restart the server. When we change this value, we need to restart the server before the change will take effect. Once we’ve restarted the server, both of these columns should show the same value.

Option 2: Use sp_configure

Another way to check the fill factor option is to use the sp_configure stored procedure. This option is a bit trickier, due to the fact that fill factor is considered an “advanced configuration option”. By default, advanced configuration options are hidden from sp_configure. But we can also use sp_configure to “unhide” them.

So, it’s basically a two step process to show the current fill factor when using this option. Actually, we also need to run RECONFIGURE, so strictly speaking, it’s probably a three step process.

But the good news is that we can do all three of these steps at once:

EXEC sp_configure 'show advanced options', '1';
RECONFIGURE;
EXEC sp_configure @configname = 'fill factor';

Output:

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

So what we did here was the following:

1. Enable the show advanced options configuration option
2. Run RECONFIGURE to install the change
3. Run sp_configure to see the current fill factor settings.

This is a riskier option, as it opens up the possibility of someone inadvertently changing the fill factor value (or the value of any other advanced option).

So we might only choose this option if we want to change the value. Note that doing so also requires a server restart, so it’s not a decision to take lightly.

Either way, it’s good practice to disable show advanced options once we’re done:

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.