Custom Default Fill Factor in SQL Server

By default, SQL Server uses a fill factor of 100%, which means that index pages are filled completely when they are created or rebuilt. In some scenarios, adjusting the fill factor to a lower value can help improve performance by leaving extra space on each page to reduce the likelihood of page splits during future data modifications.

Why Setting a Fill Factor Can Be Useful

The primary benefit of a lower fill factor has traditionally been to avoid expensive page splits, which occur when there isn’t enough space on a page to accommodate new data. When a page split happens, SQL Server must allocate a new page, copy half the data to the new page, and then insert the new data, which leads to extra I/O and CPU usage.

In environments with spinning disks, these page splits were particularly costly because of the random I/O overhead. However, with SSDs, the impact of page splits is much lower due to their ability to handle random I/O more efficiently.

That said, there are still edge cases where using a custom fill factor at the index level can be beneficial, especially for heavily updated tables where avoiding page splits remains important for write performance.

Why a Database-Wide Custom Fill Factor Can Be Problematic

  • Wasted Space and Resources: Applying a lower fill factor across all indexes can result in more index pages than necessary, increasing memory and disk space consumption.

  • Performance Trade-offs: While a lower fill factor reduces the likelihood of page splits, it may also increase the number of pages SQL Server has to scan, which can reduce read performance.

For most systems, keeping the default fill factor of 100% (or 0, which SQL Server treats as the same thing) is the best option, and custom fill factors should be applied on an index-by-index basis based on workload testing.

💡 Tip: While SSDs minimize the performance impact of page splits, a lower fill factor can still be beneficial in heavily updated indexes to avoid unnecessary write operations.

Suggested Action

Unless a specific index benefits from a custom fill factor, revert the default fill factor to 0 (100%), and apply custom fill factors only where necessary.

How to Alter the Default Fill Factor for SQL Server To change the server-wide default fill factor back to 100%, use the following SQL command:

USE [WhippetWorks];
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'fill factor', 0;
GO
RECONFIGURE;
GO