How to Change Fill Factor in SQL Server – Interview Question of the Week #080

Question: What is the ideal fill factor, and how do you change it?

Answer: There is no ideal value for every index. Zero and 100 both mean full leaf pages when a rowstore index is built or rebuilt. A lower value leaves room for later changes, at the cost of more pages to store and read.

The surprise for many people is that fill factor 0 is equivalent to 100. Zero doesn’t mean empty pages. That small detail changes how a DBA reads the default setting.

For an increasing identity key, many inserts arrive at the end of the index. Leaving space on every earlier leaf page may provide little benefit for those inserts. But the identity property alone doesn’t settle the question: updates that enlarge existing rows, deletes and the actual workload still matter. Likewise, “not an identity key” doesn’t automatically mean “use a low fill factor.”

Set It for the Index That Needs It

I would start with the observed problem. Are disruptive page splits occurring within the index? Do they justify the extra space and reads? Measure an appropriate index-level choice rather than lowering a server-wide default for every object.

This small temporary-table example records the server’s current default, then creates one index with fill factor 90. It changes no server configuration:

SELECT name, value AS ConfiguredValue, value_in_use AS RunningValue
FROM sys.configurations WHERE name = N'fill factor (%)';
IF OBJECT_ID('tempdb..#SqlaFill38650') IS NOT NULL
    THROW 50001, 'The sample table already exists in this session.', 1;
CREATE TABLE #SqlaFill38650 (ID int NOT NULL, Payload char(200) NOT NULL);
INSERT #SqlaFill38650
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 'Sample'
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
CREATE CLUSTERED INDEX CX_SqlaFill38650 ON #SqlaFill38650(ID)
    WITH (FILLFACTOR = 90);
SELECT name, fill_factor
FROM tempdb.sys.indexes
WHERE object_id = OBJECT_ID('tempdb..#SqlaFill38650') AND index_id > 0;
DROP TABLE #SqlaFill38650;

The metadata reports the index’s configured fill_factor as 90. It doesn’t promise every page remains exactly 90 percent full after the data changes. Fill factor is applied during creation or rebuild, not maintained as a continuous free-space rule. Reorganizing is not a substitute for rebuilding to apply a new setting.

The Server Default and SSMS

In SSMS, the server default lives in Server Properties, Database Settings, Default index fill factor. If you have decided to change the instance default, the following is the corresponding maintenance example. Save the previous advanced-options setting and restore it afterward. Do that even if an error stops you partway:

-- Server-default maintenance example only. Evaluate before changing it.
DECLARE @PreviousAdvanced int =
    (SELECT CONVERT(int, value) FROM sys.configurations
     WHERE name = N'show advanced options');
EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE;
EXEC sys.sp_configure N'fill factor (%)', 90;
RECONFIGURE;
EXEC sys.sp_configure N'show advanced options', @PreviousAdvanced;
RECONFIGURE;

Changing the default doesn’t rebuild existing indexes, and it takes effect after the SQL Server service restarts. An explicit index-level value takes precedence when specified. Ninety is an illustration, not my recommendation for every database.

The useful interview answer is therefore more than a number: explain what free room buys, what it costs, and how you would measure the result for the particular index.

Fill factor 0 is not empty pages, it is full pages, the same as 100.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.