fill factor Option

Use the fill factor option to specify how full Microsoft SQL Server 2005 should make each page when it creates a new index using existing data. The fill factor affects performance because SQL Server must take time to split pages when they fill up.

The fill factor is used only at the time the index is created or rebuilt. The pages are not maintained at any particular level of fullness.

The default value for fill factor is 0; valid values range from 0 through 100. When the FILLFACTOR setting is 0 or 100, the leaf level is filled to capacity. There is seldom a reason to change the default value for fill factor, because you can override it for specified indexes with the CREATE INDEX or ALTER INDEX REBUILD statements. For more information, see Fill Factor.

Note

Fill factor values 0 and 100 are identical in all respects.

The fill factor option is an advanced option. If you use the sp_configure system stored procedure to change the setting, you can change fill factor only when show advanced options is set to 1. The setting takes effect after restarting the server.

See Also

Concepts

Fill Factor
Setting Server Configuration Options

Other Resources

ALTER INDEX (Transact-SQL)
CREATE INDEX (Transact-SQL)
RECONFIGURE (Transact-SQL)
sp_configure (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance