Skip to content

Setting Custom Fill Factors Based on Page Splits

Introduction

There is a lot of talk about what fill factor should be set for an index.  We know for indexes that are ever increasing the fill factor should be set to 100% but for those indexes that are on things like last name or birthday it gets a bit trickier to decide a fill factor.  We do not want to set a default across the whole server that some has said is best practice of 90% or even worse 80% but we do not want to have a lot of page splits and index fragmentation either. Having a fill factor set to low can hurt because then you are just reading empty space into memory and storing empty data on disk.  For more information on fill factor and page splits go here.

So what is the solution?  Well it really depends.  But the solution I wrote walks you through

  • Setting the default fill factor
  • Tracking page splits
  • Lowering the fill factor

 

Setting Default Fill Factor

So first let’s set the default on the server to 100% by running the following T-SQL code:

Tracking Page Splits

Next we have to setup some things to track our page splits.  We use an extended events session to track page splits in a ring buffer and read that data into a table once a week for temporary usage.  We then take that data and translate into what index that page split came from and store that information for further processing.

First we setup an extended events session to track page splits. This is small light weight event session that I have running on servers that process over 30K transactions per second.

Next let’s create two tables in our DBA database to one read data in from the extended events session and the other to translate it into useful information:

Next setup a job that runs every hour to run the procedure to read the data from the extended events session. This procedure reads in the allocation unit and database id for each page split in the last hour from the extended events into the IndexDefragPageSplitsTemp table. Then we use some dynamix SQL to find out what index and current fill factor the current index has and store it in table IndexDefragPageSplits.

Lowering Fill Factor

Now to keep things from lowering the fill factor below a certain percentage, to set a default percent, and set the percentage of the table that has to have page splits before it lowers the fill factor I have a config table with values in them to control the process. So I’ve decided everything stays at 100% fill factor, 10% of the table would have to had page splits in a week, and the lowest fill factor that can set is 80%.

Then we setup a table to hold the index specific fill factor that it is being lowered to and have a procedure that runs weekly to populate the table and rebuild the index at the new fill factor. The procedure uses a cursor to go through each index in the table collected and lower the fill factor by 2% if the thresholds are met. Then it rebuilds the index with the new fill factor.

Conclusion

Since setting up this system we have noticed less reorganizations and rebuilds of indexes needed after a certain amount of time past and a sweet spot was found.  The percent that the fill factor is adjusted (2) could be changed to something more custom to your system or even added to the config table to really take advantage of this across a diverse environment.

4 thoughts on “Setting Custom Fill Factors Based on Page Splits

  1. Pingback: Data-Driven Fill Factors – Curated SQL

  2. SB says:

    Hi Tracy, great post, very informative and nicely done. Thanks

    Reply
    • SB says:

      Hi Tracy – I do not see minPageCount column in IndexDefragConfig table. Is it suppose to be ‘UseFillFactor’ ?

      SELECT
      @min_page_count = minPageCount,
      @SplitPagePct = SplitPagePct,
      @MinFillFactor = MinFillFactor
      FROM dbo.IndexDefragConfig;

      Thanks

      Reply
      • Tracy Boggiano says:

        Sorry I left that column out our config table has more columns used for other processes. I have corrected the post. The UseFillFactor still only suppose to be in the IndexDefragConfigSpecifics table.

        Reply

Leave a Comment