Why would you set fillfactor to a low value in this situation?

Question

Let's say we have an OLTP system using SQL Server. Let's say we have a clustered index on a table.

Please give me some answers to the following questions:

If you only insert new records that follow the index order, then page splitting would only be limited to the pages - at or after - the last page that existed at the time of your last index rebuild, correct?

In other words, if you only insert new records that follow the index order, then no page splitting would ever occur in any page that is located before (order wise) the last page that existed at the time of your last index rebuild, correct?

That is, as long as you do not try to insert records into the middle of the existing index, you will never cause page splitting to happen in any page that is located before the last page that existed at the time of your last index rebuild, correct?

If the above is all true, then isn't it also true that, in general you usually avoid inserting records into the middle of an existing index anyways? I mean, when would you want to do such a thing?

I am asking these questions, because I can't understand exactly why someone would decide to set a fillfactor to a low value for an index on an OLTP system. My thoughts are "if you have no plans to mess up the index by inserting records into the middle of the existing index, then what is the point of reserving all that unused empty space in each index page and data page if it will never get filled up?"

I understand that page splits will happen beginning from the last page (order wise) - that existed at the time of your last index rebuild - onwards. But, can you explain in more details why someone would choose to set fillfactor to a low value for an OLTP system where you have no plans to insert records into middle of the existing index?

If I am missing something in my understanding of how this all works, please let me know as well.

Thanks.

Answer 1

Yes, when appending to an index fillfactor does not help.

Yes, writing to the middle of an index in a way that causes page splits is not a good thing.

You can't always append to an index. When you index user registrations to a web forum users will input in random order: email, user name, phone, email activation key. (There will be sequential insertions as well, such as ID and registration date.)

Here, you're only choice is how to mitigate (if at all).

why someone would choose to set fillfactor to a low value for an OLTP system where you have no plans to insert records into middle of the existing index?

I wouldn't do that, indeed, but such a pure OLTP system rarely exists.

It's kind of the point of indexes to present data in a different order. Not all interesting orders are aligned with each other.

Answer 2

One somewhat contrived scenario would be if row locks escalate to page locks, fewer rows are affected because there are fewer rows per page. Though if no DML is happening, all locks should be shared locks and thus co-compatible.

Another scenario would be if you update a row such that it no longer fits on the page. Imagine the following table:

create table foobar (
   a int identity primary key clustered,
   b varchar(1000) default 'a'
);

Now, if I do:

insert into foobar default values
go 1000

I could get all of those rows on one page (more or less - assuming that the fill factor is 100, each row will take up 4+1 = 5 bytes. I know that there is row overhead, but let's make the demo easy). But, what if I do:

update foobar 
set b = replicate('a', 1000);

Now each row takes up 4 + 1000 = 1004 bytes and I can only fit ≈ 7 rows per page. I'm going to need a lot more pages! But, if the fill factor had been set lower initially, I would probably still incur some page splits (unless the fill factor was set such that there was only 1 row/page), but fewer of them. If you're on SQL 2008 or higher, I invite you to explore this with extended events - there's an event for page splits.

TL;DR - updates can also cause page splits.

Answer 3

Your understanding of FILLFACTOR matches my understanding.

If the above is all true, then isn't it also true that, in general you usually avoid inserting records into the middle of an existing index anyways? I mean, when would you want to do such a thing?

When you're not using a sequential key. If all your clustered indexes are IDENTITY(1,1) fields, then sure. However, that probably isn't the case for every table in your entire database. If you've got junction tables for many-to-many relationships, you could be adding relations all the time. You probably don't want that table to have an IDENTITY(1,1) key in that table at all. If you're instead clustering on a composite primary key of the foreign keys in the table -- because when you join that's what you'll use -- then you're not using sequential inserts at all.

Additionally, even if you use even if you use sequential keys, you might not cluster on them. It's possible and often desirable to cluster on something other than the IDENTITY column. That's going to depend on how you plan to use your data. If you're storing events but know that you want to keep events from each site together because that's how you query events 90% of the time, you might include event site in the clustering key.