14

Let's say you have a database where the default fill factor is 20. Whenever data is inserted, does it only create pages filled up to 20%?

From my understanding, when the data is inserted there will be roughly 20% of data in the pages. When the data is updated, however, it will expand to more than 20% of the index, upto filling it and generating a page split, right?

Improve this question

1 Answer 1

Reset to default
16

Fill factor only comes into play when an index is created or rebuilt. It is the amount of consumption for the index of the leaf level pages that are filled during these operations. (see the note below for more clarification on affected page levels)

When there is a DML command to data (INSERT, UPDATE, and/or DELETE), it will happen to the corresponding affected indexes. In other words, if you have a page that is 20% filled and you insert data into that page, the page will contain more than 20% of the data (let's say 35% just for example's sake). Do another insert, now the page is 64% filled. Rebuild the index, and the leaf level pages will now relatively contain the space percentage that you specify (or implicitly the default value for the server).

(Note, when you don't specify PAD_INDEX to be ON, fill factor is only applied to leaf level pages. But when you set PAD_INDEX = ON, the fill factor will be taken into account for intermediate-level pages of the index. The default is OFF)

The reason to adjust fill factor (instead of using the default 100/0) is so that you minimize page splits when inserting or updating data. But keep in mind, nothing is for free. The lower the fill factor, the more space data would normally take up. If you keep an 80% free page space for your indexes, they will consume a relatively larger amount of disk space, which can lead to more reads.

From my understanding, when the data is inserted there will be roughly 20% of data in the pages. When the data is updated, however, it will expand to more than 20% of the index, upto filling it and generating a page split, right?

When data is inserted it will insert into the appropriate indexes at the appropriate page. This could and most likely would very well cause the page consumption to be higher than the fill factor.

A page split will happen when new data is added to a full index page. SQL Server will then split the page and approximately place half of the data from the full page into a new page. Again, fill factor does not come into play here.

A legitimate reason to lower fill factor would be to minimize page splits, therefore minimizing index page fragmentation.

Improve this answer
5
  • 3
    It also minimizes IO operations needed to grow or allocate space.
    – JNK
    Commented Mar 23, 2012 at 17:42
  • OK, so I was wrong with how the behavior worked. Thank you for such a detailed answer!
    – DForck42
    Commented Mar 23, 2012 at 19:10
  • 1
    @DForck42 No problem, glad to help.
    – Thomas Stringer
    Commented Mar 23, 2012 at 20:47
  • Can we summarize this to say that setting a low fill factor will tend to slow reads (more pages) but speed inserts (fewer splits)?
    – Jon of All Trades
    Commented Apr 2, 2012 at 20:05
  • 2
    @Jon: With high fillfactor indexes fragment and reads slow down. For every index there is an optimal fillfactor -- above it and below it slow writes and reads. Optimality depends usage patterns (how many inserts per day), maintenance patterns (how often its rebuild), data (how unique is the key). Non-unique indexes tend to require more free space (lower fillfactor).
    – wqw
    Commented Apr 3, 2012 at 12:51

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.