Recently I saw a question posted about what fillfactor a person should choose when building a table. The poster seemed confused about what this meant, and I can see where it becomes hard for someone to understand it. Anytime you deal with ratios of things it's easy to get turned around as to which side of the ratio you are dealing with. If you want a refresher, here's an entry in BOL and an article as well.
Some time ago I had the debate with someone about what the default fillfactor should be. It is 0 in 2008 (pages filled), but I'm not sure that's the best choice. Many people don't track page splits, or even know the ratio of reads to writes. Perhaps most applications are so heavily read weighted that this makes sense, but my feeling is that something more like 85 or 90 would be better.
But that's just a feeling, and I don't have data to back that up. But it's Friday, and I was looking for a good poll to ask, so….
How do you decide what fillfactor to choose? (Give us your answer)
Or do you even check or change it? Maybe it's not something you've ever looked at for busy tables or because of performance. That's not necessarily bad, but I'd be curious to know how many people never look at this.
If you do examine fillfactor, then what influences your choices? What counters or measures do you use to decide what values to set. The more scientific a method you have, the more help it is to others.
For me, I haven't looked at this too much in my career. Only in places where we are loading or changing data often has it been something to worry about. Perhaps that means it isn't a big deal, but it has helped in places. When we have large data changes, and we can't necessarily reindex as often as we'd like, I've experimented with turning the fill factor down as low as 30.
I've typically examined the row size v page size to decide how many changes I can fit on a page given some fill factor as well as looking at the clustered index to decide where my data changes will end up. If you always add data to the end of the table, then a low fillfactor just wastes reads. If you're inserting all throughout the space, then fillfactor makes a bigger difference. I've also watched page splits to give me feedback on how my changes are affecting the system.
The biggest indicator, however, has been my phone. It usually rings when I've made a change that worsens performance.
Steve Jones
The Voice of the DBA Podcasts
The podcast feeds are available at sqlservercentral.mevio.com. Comments are definitely appreciated and wanted, and you can get feeds from there.
You can also follow Steve Jones on Twitter:
or now on iTunes!
- Windows Media Podcast - 32.7MB WMV
- iPod Video Podcast - MB MP4
- MP3 Audio Podcast - 5.6MB
Today's podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.
I really appreciate and value feedback on the podcasts. Let us know what you like, don't like, or even send in ideas for the show. If you'd like to comment, post something here. The boss will be sure to read it.