A Self-Tuning Fill Factor Technique for SQL Server

Mike Byrd

Have you ever created an index and wondered what to specify for the fill factor? Usually, I’ve never given it a worry and just left it at the default value = 0 (actually, this is a synonym for 100). In a universe far, far away, I worked at the Air Force Rocket Propulsion Laboratory, Edwards AFB, CA. There I developed an early, early version AI technique to optimize a scenario with many variables. Now, I “re-developed” that technique to determine a “near” optimum fill factor value for almost every index in a database. I say “near” because it won’t always derive the perfect fill factor, but it will get you close. We will review the updated T-SQL for this technique for a very active OLTP database. I observed a decrease in overall database wait times of 30% within a few months. The continuous tuning allows for changes in data skew and evolving application interactions with the database. IMHO, this is a real breakthrough that will make your indexes perform even better. All code will be available for download.