In a recent post, we discussed index fill factor and what it is. We want to follow up that post with an explanation of when and why you may want to set a fill factor for an index or change the default fill factor on your instance of SQL Server.

Often when you are working with indexes and fill factors, a common question arises: what setting should you use for the fill factor on this index? We wish that we could give you an easy method or a formula that would help you determine the correct setting. But there isn't one; you'll have to determine your setting through trial and error. What we can give you is the method we use to determine the fill factor for clustered indexes. It is common practice to leave the fill factor for nonclustered indexes at the default setting.

Clustered Index Fill Factor

If you've been around SQL Server for a while, you may have heard the "clustered index, no clustered index" debate. One of the reasons the anti-clustered-index group relied upon in their arguments was the issue with page splits during data loads. Page splits can be a source of performance bottlenecks. To reduce this potential source of performance issues, fill factor comes in handy.

Note: You can read more about page splits in our first post on fill factor.

Using Page Splits and Fill Factor Together

To determine the correct fill factor for a clustered index, we can look at the number of page splits that occur during a load process or over a period of time:

  1. Take a baseline of the number of page splits during a load or over a set period. The period is up to you, but it should be short enough that you can easily repeat it.
    You can capture the number of page splits for an index by watching the logical fragmentation changes of the index.
    2. SELECT avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(database_id,object_id,1,NULL,'DETAILED')
  2. Rebuild your clustered index with a slightly lower fill factor.
    To rebuild the index with a different fill factor, you can use ALTER INDEX.
    3. ALTER INDEX index_name ON object_name REBUILD
(WITH FILLFACTOR = xx)
  3. During the next load or set period, record the number of page splits again, and compare the new number to the baseline. Did the number go down? Did the number go down enough to stop the page splits?
  4. Keep rebuilding the clustered index with a slightly lower fill factor until the number of page splits starts going down. Over time and with multiple adjustments, you will see the change in the number of page splits start to level off. At that point, the current setting for the fill factor is the correct one for that clustered index.

Then, start looking at your next clustered index, and repeat these steps.

Summary

Adjusting the fill factor for individual clustered indexes is something that you should do in response to page splits. There is no magic formula to determine exactly how much lower the fill factor should be. But, you can use this simple process to help you determine which fill factor seems to fit into that "sweet" spot that may still allow page splits but not so many of them that they interfere with the performance of your database.