SQL Friday #6: Mike Byrd on “A Self-Tuning Fill Factor Technique for SQL Server”

Online Microsoft Data Platform Lunch Sessions

SQL Friday #6: Mike Byrd on “A Self-Tuning Fill Factor Technique for SQL Server”

2020-06-17 Past SQL Friday Sessions 0
Time:12:00 CEST
How to attend:Already finished
Accompanying material:Download from GitHub
If you can’t attend this one but want to attend future sessions, join the Meetup group and be the first to know when registration comes online: https://www.meetup.com/SQL-Friday

A Self-Tuning Fill Factor Technique for SQL Server

Have you ever created an index and wondered what to specify for your fill factor? Usually most of us never ever give it a worry and just leave it at the default value = 0 (actually this is a synonym for 100).

Recently I’ve discovered a technique that establishes a “near” optimum fill factor value for every index in a database. I say “near” because it may not always derive the perfect fill factor, but will get you close.

We will look closely at the T-SQL for this technique to see how the proof of concept was developed for a very active OLTP database. The code “tunes” on both index fragmentation and bad page splits. A decrease in overall database wait times of 30% was achieved within 60 days after the tuning was initiated. The tuning is continuous and allows for changes in data skew as well as evolving application interactions with the database.

This is a real breakthrough for making your indexes and queries perform even better. All code will be available for download.

Level: Advanced

Prerequisites: Some knowledge of index fragmentation, page splits, and T-SQL.

About Mike

Former rocket scientist and USAF Fighter Pilot — now a SQL Server geek. Past opportunities include free-lance technical writer for PC Magazine, Senior Software Manager for government sponsored munitions effectiveness committee, and IT Director for Texas Windstorm Insurance Association (9 years), Currently senior database consultant for several companies. Presenter at many SQL Saturdays in US, Europe, and Australia.


SQL Server performance tuning; database (OLTP & OLAP) architecture; trouble-shooting; teaching TSQL (Basic through Advanced).