Setting Appropriate Fill Factors for Optimal Performance

Introduction

Indexing is an essential component of database performance tuning in SQL Server. It enables the efficient retrieval of data by creating a data structure that maps the values in a specific column or set of columns to their corresponding rows in the table. One critical aspect of index management is setting appropriate fill factors, which can significantly impact the performance of your database. In this article, we will explore the concept of fill factors, how to set them, and their role in optimizing SQL Server indexing.

Understanding Fill Factors

A fill factor is a percentage value that determines how full SQL Server should make each leaf-level index page when an index is created or rebuilt. The remaining space is left free so that rows can be inserted into, or grow within, existing pages without forcing a page split. Fill factor values range from 1 to 100, with a default value of 0 (which is treated the same as 100). A higher fill factor value results in less free space on the index pages, whereas a lower fill factor value leaves more free space.

It is important to understand that the fill factor is applied only when an index is created, rebuilt or reorganized. SQL Server does not maintain it as data is modified, so as rows are added the free space is consumed and the pages fill up again. By default the fill factor applies only to the leaf level of the index. To apply it to the intermediate levels as well, use the PAD_INDEX = ON option.

The fill factor is a balancing act between storage and I/O efficiency on one side and page splits and fragmentation on the other. A high fill factor value (leaving less free space) packs more rows onto each page, which reduces storage requirements and the number of pages that must be read, improving query performance. The downside is that pages have little room for new or growing rows, so data modifications cause more page splits, which increases fragmentation and index maintenance. On the other hand, a low fill factor value (leaving more free space) reduces page splits and fragmentation, but it spreads the same data across more pages. That means more storage, more I/O to read the index, and less efficient use of memory, which can also degrade query performance.

Setting Appropriate Fill Factors

When setting fill factors, it's essential to consider your database's specific workload characteristics and data modification patterns. Here are some general guidelines to help you determine the appropriate fill factor for your indexes:

Default fill factor (0 or 100): This setting is suitable for tables with infrequent data modifications (INSERT, UPDATE, or DELETE statements), read-heavy workloads, and indexes where new rows are always added at the end, such as a clustered index on an ever-increasing identity column. Since the index pages are filled to capacity, this setting gives the best storage utilization and read performance. However, it may lead to page splits and increased fragmentation over time if rows are inserted into the middle of the index or existing rows grow.

Lower fill factor values (e.g., 70-90): These settings are appropriate for tables with frequent data modifications, especially when the modifications occur in the middle or at the beginning of the index, such as inserts into an index on a random key (a GUID, for example) or updates that make rows wider. A lower fill factor leaves more free space on index pages, reducing the likelihood of page splits and minimizing fragmentation. However, this approach requires more storage space and results in increased I/O operations. A lower fill factor will not help an index where all the inserts go to the end, so don't pay the storage cost for nothing.

Adjusting fill factors over time: As your database workload evolves, it's essential to monitor and adjust fill factors accordingly. Regularly review index fragmentation levels (for example with sys.dm_db_index_physical_stats), page split activity and performance metrics to determine whether the current fill factor settings are still appropriate. Remember that a new fill factor only takes effect when the index is rebuilt. Adjusting the fill factor based on real-world data can help you achieve the optimal balance between storage utilization and index maintenance.

Examples

Create a new index with a specific fill factor:

CREATE INDEX YourIndexName ON dbo.YourTableName(YourColumnName) WITH (FILLFACTOR = 80);

In this example, the fill factor is set to 80, meaning the leaf-level index pages will be 80% full, leaving 20% free space for future growth.

Modify an existing index's fill factor:

ALTER INDEX YourIndexName ON dbo.YourTableName REBUILD WITH (FILLFACTOR = 70);

In this example, the fill factor is set to 70, meaning the leaf-level index pages will be 70% full after rebuilding, leaving 30% free space for future growth. The new value is also stored with the index, so later rebuilds that don't specify a fill factor will reuse it.

Create a clustered index with a specific fill factor:

CREATE CLUSTERED INDEX YourClusteredIndexName ON dbo.YourTableName(YourColumnName) WITH (FILLFACTOR = 90);

In this example, the fill factor is set to 90, meaning the leaf-level index pages (which for a clustered index are the data pages themselves) will be 90% full, leaving 10% free space for future growth.

Set the default fill factor for the server:

SQL Server has no database-level fill factor setting. The default is a server-wide option, which you set with sp_configure:

EXEC sp_configure 'show advanced options', 1; RECONFIGURE; EXEC sp_configure 'fill factor (%)', 75; RECONFIGURE;

In this example, the default fill factor is set to 75, meaning any index created on the server without an explicit FILLFACTOR will have its leaf-level pages 75% full, leaving 25% free space for future growth. Note that this setting does not affect existing indexes, and the SQL Server service must be restarted before the change takes effect. Because it applies to every database on the instance, it is usually better to leave the server default at 0 and set the fill factor explicitly on the indexes that need it.

Conclusion

It's important to choose a fill factor that strikes a balance between minimizing page splits and avoiding excessive wasted space. Monitor your SQL Server's performance using the Vroom Performance Suite and adjust the fill factor as needed to achieve optimal results.