Fill Factor

Fill factor is an optional setting for creating indexes in SQL Server. It allows you to define how full the SQL Server Database Engine should make each index page.

SQL Server is set by default to use a 100% fill factor, and will therefore try to fill up all the pages in indexes as full as possible. When you create or rebuild an index, you can, however, select a specific fill factor to instruct SQL Server regarding the percentage of each data page in the “leaf” level of the index it should fill up.

If you set an 80% fill factor when rebuilding a clustered index that means 20% of each leaf page will be empty. Keep in mind that that the empty space will take up the same amount of space as it sits in memory.

The general rule of thumb in the SQL Server playbook is lowering the fill factor improves performance by reducing page splits, and this is in fact true. However, decreasing the fill factor also decreases performance because your data is so spread out so you constantly have to read more pages into memory and all the wasted space in cache that’s just sitting there empty.

According to SQL Server guru Kendra Little, there’s almost never a good reason to set your fill factor below 80%, and leaving the default 100% fill factor is actually the best option in many cases.

Idera’s SQL Defrag Manager makes changing the fill factor settings a snap. Undertaking this kind of proactive index management reduces the need for index rebuilds and improves the performance of your SQL Server applications.