Setting fillfactor to a low value provides a temporary performance enhancement. Its benefits fade as inserts to the database increase the amount of space used on data or index pages.
A lower fillfactor provides these benefits:
It reduces page splits on the leaf-level of indexes, and the data pages of allpages-locked tables.
It improves data-row clustering on data-only-locked tables with clustered indexes that experience inserts.
It can reduce lock contention for tables that use page-level locking, since it reduces the likelihood that two processes will need the same data or index page simultaneously.
It can help maintain large I/O efficiency for the data pages and for the leaf levels of nonclustered indexes, since page splits occur less frequently. This means that eight pages on an extent are likely to be sequential.
