DBA Definitions: FILLFACTOR

1

November 6, 2017 by Kenneth Fisher

It was recently brought to my attention that not everyone knows everything. This was a shock. Everyone is born knowing the basics though right? Right??

I was sure everyone at least knew what FILLFACTOR is. But based on some rumors I’ve been hearing, it seems not.

What exactly is FILLFACTOR? It’s the amount of space (percentage) left empty in the leaf/data level pages of an index during creation or a REBUILD.

That’s probably going to require a bit more explanation, isn’t it? So let’s break it down.

leaf level pages : Almost everything in SQL Server is stored in 8k pages. Indexes are stored in

  • non-leaf pages : The root and intermediate pages that direct SQL to the right information.
  • leaf/data pages : The bottom of the index where the data (in clustered indexes) or primary key (to point back to the actual row in a non-clustered index) and included columns exist
  •  
    Note: FILLFACTOR doesn’t affect the intermediate level pages unless PAD_INDEX is turned on. (see below)

    creation or REBUILD : FILLFACTOR ONLY effects things during the index creation and REBUILD. So when a page gets full and has to split the new pages aren’t affected by FILLFACTOR. This would be one of the reasons why periodic REBUILDs are a good idea for busy tables.

    Conclusion

    When creating/rebuilding an index a high FILLFACTOR will reduce the size of the index/table (non-clustered/clustered index). So for example, for a read-only table, I’d recommend a FILLFACTOR of 100 (max number of rows possible in the leaf pages). However, with a table that has a high number of inserts/updates you’ll want a lower FILLFACTOR to avoid page splits. (In case you didn’t know page splits are slow.)

    Now just to make things more complicated think about this. How important is FILLFACTOR on an ever increasing index? Say an index on createdate?

    Bonus: PAD_INDEX

    PAD_INDEX is a flag that causes the FILLFACTOR value to be used on intermediate pages. I’m not going to say too much about it except to say that Brent Ozar (b/t) says that Kimberly Tripp (b/t) says:

    Typically, PAD_INDEX is not needed unless you have extremely unbalanced activity and are unable to rebalance the tree (meaning you need to achieve high availability).

    In other words, if you don’t know that you should be using this setting, you probably shouldn’t be using it.

One thought on “DBA Definitions: FILLFACTOR

  1. Michael Tormos says:

    “In other words, if you don’t know that you should be using this setting, you probably shouldn’t be using it.”

    Such a universal truth ..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,146 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: