What is the best value for Fill Factor in SQL Server?

Introduction

When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0, which means that the leaf-level pages are filled to capacity. Fill-factor values 0 and 100 are the same in all respects. The fill-factor setting applies only when the index is created, or rebuilt.

How does it help?

The fill-factor option is provided for fine-tuning index data storage and performance. In an insert-intensive environment, the index pages will eventually split to accommodate additional entries. To avoid or reduce the frequency of page splits, the index should be rebuilt using an appropriate fill factor.

What is the ideal value?

It depends on the ratio of reads to writes that your application makes to your SQL Server tables. As a rule of thumb, we might follow these guidelines:

•          Low Update Tables (100-1 read to write ratio): 100% fill factor
•          High Update Tables (where writes exceed reads): 50%-70% fill factor
•          Everything In-Between: 80%-90% fill factor.

Hope, this small article will help you in identifying the fill-factor for your application/system.

Next, I would come up with a handy script to update the fill-factor of all the existing indexes at one go.