That old fill factor setting deserves an explanation before a reset. A custom fill factor also spreads existing rows across more pages. Finding the setting is easy; deciding whether it still helps needs workload evidence.
Inventory Each Custom Fill Factor Before Changing It
An old tuning choice can outlive the workload that justified it. The table grows, the insertion pattern changes, and a maintenance job keeps rebuilding with the same percentage. That does not prove the setting is wrong. It tells you which indexes deserve an explanation.
I start with the catalog value and the current workload owner. A setting of seventy is not a confession. It can describe a deliberate choice for inserts distributed through the index key. It can also describe an inherited maintenance default applied without measurement. The query cannot distinguish those histories by itself.
The following report lists rowstore indexes on user tables. It aggregates partition statistics before joining them to index metadata. That produces one row per index, rather than one row per partition. Used pages and reserved pages provide size context without requiring a detailed physical scan.
WITH Sizes AS
(
SELECT object_id, index_id,
SUM(used_page_count) AS UsedPages,
SUM(reserved_page_count) AS ReservedPages
FROM sys.dm_db_partition_stats
GROUP BY object_id, index_id
)
SELECT SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS TableName, i.name AS IndexName,
i.fill_factor, s.UsedPages, s.ReservedPages,
CAST(s.UsedPages*8.0/1024 AS decimal(18,2)) AS UsedMB
FROM sys.indexes i
JOIN sys.objects o ON o.object_id = i.object_id
JOIN Sizes s ON s.object_id = i.object_id
AND s.index_id = i.index_id
WHERE o.type = 'U' AND o.is_ms_shipped = 0
AND i.type IN (1,2)
AND i.fill_factor NOT IN (0,100)
ORDER BY s.UsedPages DESC, SchemaName, TableName, IndexName;Read the Server Default Separately
Inspect the server-wide configuration without changing it. The configured value and active value can differ. Keep both in the report so a pending administrative change does not disappear from the conversation. This is an inventory step, not permission to run sp_configure.
SELECT name, value AS ConfiguredValue,
value_in_use AS ActiveValue,
is_dynamic, is_advanced
FROM sys.configurations
WHERE name = 'fill factor (%)';The server default supplies a value when a new index does not specify one. Existing index settings are not automatically rewritten when the server option changes. A rebuild can retain the index's existing setting. Simply omitting FILLFACTOR is therefore not a reliable way to erase a historical custom value.
Values zero and one hundred represent full leaf pages for the explicit index option. Do not confuse that with an arbitrary server default chosen by an administrator. Decide whether the approved target means fully packed pages or the organization's current default percentage. Those are separate decisions when the server setting is lower than one hundred.
The Space and Split Trade-Off of a Custom Fill Factor
Fill factor controls the initial packing of leaf pages during creation or rebuild. It does not reserve a permanent percentage that SQL Server continuously restores. Inserts and expanding updates consume the available room. The index's later page density depends on the actual changes it receives.
A lower percentage creates more pages for the same starting data. Those pages consume storage and buffer memory. Range scans can read more pages, even when the rows returned are unchanged. Page counts in the inventory show the current footprint, not the footprint a future rebuild will guarantee.
The potential benefit concerns growth within existing pages. If inserts land throughout the key range, spare room can reduce immediate page splitting. A steadily increasing key has a different pattern, with new rows concentrated near the end. Lowering fill factor across every existing page does not directly solve every form of last-page contention.
Add Measurements to the Inventory
Review logical reads from important statements and their actual plans. Capture a representative interval of index activity before choosing a new percentage. Operational counters can help describe inserts, updates, and allocations, but their lifetime is limited. Record the capture time and restart context with any counter comparison.
SELECT i.name, SUM(os.leaf_insert_count) AS LeafInserts,
SUM(os.leaf_update_count) AS LeafUpdates,
SUM(os.leaf_allocation_count) AS LeafAllocations
FROM sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) os
JOIN sys.indexes i ON i.object_id = os.object_id
AND i.index_id = os.index_id
WHERE os.object_id = OBJECT_ID(N'dbo.FillFactorReviewDemo')
GROUP BY i.name;This query returns nothing until the demonstration table exists. Leaf allocations provide a useful clue about rowstore page activity. They are not a complete count of expensive mid-page splits in every scenario. Pair them with workload measurements instead of translating each allocation into a fixed performance penalty.
I also check scheduled maintenance for the percentage it applies. Otherwise a carefully reviewed rebuild can be undone by the next job. Which process currently owns this index setting, and what evidence would justify keeping it?
Rebuild One Disposable Index First
Use a separate demonstration table to make the catalog change concrete. This example starts with a custom setting of 70 and rebuilds with FILLFACTOR = 100. It does not select an index from the inventory automatically. No production index is changed by these statements.
CREATE TABLE dbo.FillFactorReviewDemo
(
ReviewID int NOT NULL PRIMARY KEY,
LookupValue int NOT NULL
);
INSERT dbo.FillFactorReviewDemo VALUES (1,10),(2,20),(3,30);
CREATE INDEX IX_FillFactorReviewDemo_Lookup
ON dbo.FillFactorReviewDemo(LookupValue)
WITH (FILLFACTOR = 70);
SELECT name, fill_factor FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.FillFactorReviewDemo');
ALTER INDEX IX_FillFactorReviewDemo_Lookup
ON dbo.FillFactorReviewDemo
REBUILD WITH (FILLFACTOR = 100);
SELECT name, fill_factor FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.FillFactorReviewDemo');The two catalog queries show 70 before the rebuild and 100 after it. The catalog stores the explicit 100, not the 0 that an index created without the option shows. Both mean full leaf pages, and the inventory query excludes both.
For an approved organization-specific default, substitute that reviewed percentage explicitly. Do not assume leaving the option out resets it. Rebuilding consumes resources and can acquire blocking locks. Edition, index type, and chosen options determine the available online behavior.
Preserve a Deliberate Custom Fill Factor
Document the reason for a low value before resetting it. An actively changing random key, expanding rows, and a demonstrated split problem deserve a targeted decision. A tiny rarely used index deserves a different level of attention than a large index supporting critical range scans.
Compare after the change over the same representative workload interval. Check read cost, write behavior, and growth rather than just the catalog value. Keep the previous definition so the approved setting can be restored if the measured trade-off is worse. The useful output is a justified index policy, not a report with every percentage made identical.
Avoid Mixing Maintenance Objectives
Fill factor, fragmentation, and page density describe related but different properties. Fragmentation concerns the logical order of pages. Density concerns how much useful content each page holds. The stored fill factor describes the instruction used at creation or rebuild. A low stored percentage does not tell you today's exact density.
Do not use this report as a list of indexes that must be rebuilt immediately. A rebuild also changes statistics and consumes transaction log space. If performance changes afterward, those effects can complicate the explanation. Test one justified change with captured plans and comparable inputs. Retain the original percentage in the change record. Separating these objectives makes the result easier to interpret and prevents routine maintenance from becoming an unexplained series of experiments.
A custom fill factor needs a workload reason that survives the next maintenance cycle. Preserve the previous custom fill factor with the approved change so its original choice remains recoverable.
Related reading on this blog: Fill Factor: Instance Level or Index Level and Your Index Rebuild Maintenance Plan Is Rebuilding Indexes Nobody Uses.
A custom fill factor is not proof of wasted space, it is a workload choice that needs a current reason and measured trade-offs.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
