fill factor Option

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

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

The default for fill factor is 0; valid values range from 0 through 100. A fill factor value of 0 does not mean that pages are 0 percent full. It is treated similarly to a fill factor value of 100 in that SQL Server creates clustered indexes with full data pages and nonclustered indexes with full leaf pages. It is different from 100 in that SQL Server leaves some space within the upper level of the index tree. There is seldom a reason to change the default fill factor value because you can override it with the CREATE INDEX statement.

Small fill factor values cause SQL Server to create new indexes with pages that are not full. For example, a fill factor value of 10 is a reasonable choice if you are creating an index on a table that you know contains only a small portion of the data that it will eventually hold. Smaller fill factor values cause each index to take more storage space, allowing room for subsequent insertions without requiring page splits.

If you set fill factor to 100, SQL Server creates both clustered and nonclustered indexes with each page 100 percent full. Setting fill factor to 100 is suitable only for read-only tables, to which additional data is never added.

fill factor is an advanced option. If you will be using 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 stopping and restarting the server.

To set a fixed fill factor