Fill Factor


Can someone explain to me what Fill Factor is and what is the rule of thumb when specifing it's value on MSSQL 6.5? I've looked at three books on MSSQL and they all give different answers to what it should be set to.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

It's description from Books online:

fill factor
Determines how full SQL Server makes each page when it is creating a new index on existing data (unless the user specifies some other value using the CREATE INDEX statement). 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 and becomes less important as changes to the data are made. The pages are not maintained at any particular level of fullness.
The default for this configuration option is 0; legal values range from 0 through 100. A fill factor of 0 does not mean that pages are 0 percent full. It is treated similarly to a fill factor 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 a space within the index B-tree. There is seldom a reason to change the default fill factor, especially since you can override it with the CREATE INDEX statement.
If fill factor is set to 100, SQL Server creates both clustered and nonclustered indexes with each page 100 percent full. A fill factor of 100 makes sense only for read-only tables _ tables to which no additional data will ever be added.
Smaller fill factor values cause SQL Server to create new indexes with pages that are not full. For example, a fill factor 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.

Normally, the index management process leaves room for two additional rows on each index page. When you set FILL FACTOR to 100 percent, it no longer leaves room for these rows. The only effect on these calculations is on calculating the number of clustered index pages (Step 4) and calculating the number of nonleaf pages (Step 4). Both of these calculations subtract 2 from the number of rows per page. When using a FILL FACTOR of 100 percent, do not subtract 2 from the number of rows per page.

The value of Fill Factor contained in field OrigFillFactor sysindexes table. In the further constant size of filling of pages is not supported. By default fill factor is applied only to the leaf level of B-Tree index (for clustered index - on data) .Intermediate pages are filled wholly, there is a place only for one record, and in ununique clustered index - for two rows. On filling intermediate index page the splitting is made.

My advice - do not change meaning of this option. If your table will contain only read only data, establish meaning of Fill Factor to 100%.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I have not told yet, that if you want the FILLFACTOR setting should be applied to the index node pages
(not only leaf) you may use WITH PAD_INDEX option when create the index.
It's example from Books online:

This example creates an index on the author's identification number in the authors table. Without the PAD_INDEX option, SQL Server version 6.5 creates leaf pages that are 10 percent full, but the node pages are filled almost completely. With PAD_INDEX the node pages are also 10 percent full.

Note  At least two entries appear on the index pages of unique clustered indexes when PAD_INDEX is not specified.

CREATE INDEX au_id_ind
        ON authors (au_id)
jvieiraAuthor Commented:
Much better explanation than the books I read.  Thanks.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.