Index Fill Factor Explicitly Set

This warning has been raised because the index fill factor has been explicitly set. The server-wide default fill factor is configured via sys.configurations, but in some cases, indexes can benefit from a non-default fill factor.

Suggested Action

šŸ’” Aireforge Advisor can automatically generate a script for this.

Set the fill factor of all indexes to be zero (which means to use the server default) unless testing has proven the change to be effective.

If the index fill factor is set to a value different to the server default:

Ensure that this index is supposed to have a customer fill factor. If not, reset to 0.

If the index fill factor is explicitly set to the server default:

Although not an immediate issue, if the database default is changed and indexes are rebuilt, there may be the expectation that this index will take on the new fill factor. So consider reverting fill factor back to the database default.

How to Specify an Index Fill Factor in SQL Server

ALTER INDEX IX_IndexName ON TableName
REBUILD WITH (FILLFACTOR = 0);

How to Remove an Explicitly Set Index Fill Factor in SQL Server

-- Drop and recreate the index without specifying the fill factor.
DROP INDEX [IX_IndexName] ON [TableName]
GO
CREATE INDEX [IX_IndexName] ON [TableName] (TableID)

Further Reading

Configure the Fill Factor Server Configuration Option | Microsoft Docs

Specify Fill Factor for an Index | Microsoft Docs