If you’re getting an error that reads “The configuration option ‘fill factor’ does not exist, or it may be an advanced option” in SQL Server, it appears that you’re trying to view or set the default fill factor configuration option.

In SQL Server, fill factor is considered an advanced configuration option. By default, advanced options aren’t available for viewing and changing. However, we can use the following technique to make them available.

Example of Error

First, let’s take a look at code that produces the error:

EXEC sp_configure @configname = 'fill factor';

Output:

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62
The configuration option 'fill factor' does not exist, or it may be an advanced option.

Here, I tried to use sp_configure to view the current fill factor setting, but I got an error.

This error occurred because SQL Server has designated fill factor (among others) as an advanced configuration option, but my system isn’t currently configured to access such options.

Solution

SQL Server provides us with the means to view advanced configuration options. In fact, we do this via another configuration option; the show advanced options configuration option.

By default the show advanced options configuration option is set to 0, which means that it’s off, or disabled. We can enable this option 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 had to run RECONFIGURE as one last step. Given we already did that, we can ignore the part of the output message that tells us to run it.

Anyway, let’s run the initial code that had previously caused an error:

EXEC sp_configure @configname = 'fill factor';

Output:

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

We can now see the setting for the fill factor configuration option.

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;

Done.