SQL Friday #6: Mike Byrd on “A Self-Tuning Fill Factor Technique for SQL Server”
| Date: | 2020-07-10 |
| Time: | 12:00 CEST |
| How to attend: | Already finished |
| Accompanying material: | Download from GitHub |
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.
Specialties
SQL Server performance tuning; database (OLTP & OLAP) architecture; trouble-shooting; teaching TSQL (Basic through Advanced).
