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.

Filed under: Index, Microsoft SQL Server, SQLServerPedia Syndication Tagged: indexes, microsoft sql server