5

The default FILLFACTOR is 100 and I have been told at times to reduce it to 80 or lower so that page splits will be reduced due to the empty space on each page.

I don't understand this--if a new index is created on a table with a default FILLFACTOR of 80, 20% space of each page is left empty. So assume a row needs to use 10% of a page, so when it's full it can hold 10 records. With a FILLFACTOR of 80, now it can only hold 8 records. So when you add the 9th record, will there be a page split? With 100% FILLFACTOR, a page split will only happen on the 11th record, so it seems that a lower fill factor makes it worse.

Improve this question
0

3 Answers 3

Reset to default
3

The fill factor does not apply to new rows being added to a page. The fill factor only applies to how full a page is filled when the index is created, rebuilt, or reorganized.

Accordingly, if the index is rebuilt or reorganized on a scheduled basis, your index will be able to accommodate new rows with fewer page splits if the fill factor is set appropriately.

Improve this answer
0
3

Fill factor does not impact “regular” inserts/updates. It tells the engine to leave some space while building, rebuilding, or reorganizing the index.

This is not 'reserved' and will fill up with DML activity.

It doesn't protect you against page splits, it simply postpones them a bit, but it depends on the key and insert patterns.

For example, for an identity column or other ever-increasing data, page splits can't occur due to inserts, and it does cause a waste of memory space so be careful.

I wouldn’t modify it, unless I had a compelling reason to.

Improve this answer
0
0

From A SQL Server DBA myth a day: (25/30) fill factor by Paul Randal:

The fill-factor setting applies only when the index is created, rebuilt, or reorganized. The SQL Server Database Engine does not dynamically keep the specified percentage of empty space in the pages.

Trying to maintain the extra space on the data pages would defeat the purpose of fill factor because the Database Engine would have to perform page splits to maintain the percentage of free space specified by the fill factor on each page as data is entered.

Other useful articles:

Improve this answer
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.