Fill Factor

Static Tables – Set Fill Factor at 100 (or default server fill factor),

As these tables are never changing, keeping the Fill Factor at 100 is the best option. They conserve the space, and also there is no fragmentation.

Tables Updated Less Often – Set Fill Factor at 95.

These tables are more or less having characteristics like static tables. As they are not updated often, they do not encounter much issues. To accommodate the updates, I like to keep the Fill Factor 95. Honestly, if you are rebuilding the indexes at regular intervals, then I would prefer a Fill Factor of 95.

Frequently updated Tables – Set Fill Factor between 70 and 90.

When I have to set the Fill Factor at the table level, I first start from 90 and observe the table for a while, If I notice that there is still a recurring issue with page split, which in turn leads to fragmentation, I lower it further down towards 70 with an interval of the 5 at one times. Fill factor has to main balance between reads/writes.

Tables with Clustered Index on Identity Column – Set Fill Factor at 100.

This is very often seen in an OLTP system. Many tables have the identity column as a clustered index. In this case, all the new data is always inserted at the end of table and a new row is never inserted in the middle of the table. In this situation, the value of Fill Factor does not play any significant role and it is advisable to have the Fill Factor set to 100.