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.

Tracy Boggiano
Follow me

Tracy Boggiano

Database Superhero at ChannelAdvisor
Tracy has spent over 20 years in IT and has been using SQL Server since 1999 and is currently certified as a MCSE Data Management and Analytics. She has worked on SQL Server 6.5 and up including currently SQL 2017 RC1. She enjoys monitoring, performance tuning, and high availability and disaster recovery technologies. Tracy is currently a co-organizar the for special interest group for Advanced DBA Topics for the TriPASS user group.

She also tinkered with databases in middle school to keep her sports card collection organized.

Tracy has volunteered through the NC Guardian ad Litem program since 2003 advocating for abused and neglected foster children in court.This is her passion outside of SQL Server and favorite job.More information about this program in North Carolina can be found at http://volunteerforgal.org or the national organization CASA at http://www.casaforchildren.org.
Tracy Boggiano
Follow me