Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Choosing a FillFactor

By Steve Jones,

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

Everyday Jones

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:

Overall RSS Feed: or now on iTunes!

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.

Total article views: 432 | Views in the last 30 days: 4
 
Related Articles
FORUM

fillfactor

fillfactor

BLOG

Podcasting

A new video setup is on the way!!!! Actually I'll do a couple podcasts on podcasting over the hol...

BLOG

Podcast Upgrades

A minor change for the podcasts next week. I got my wireless microphone, and I'm working with it a b...

ARTICLE

Podcast Announcements

Podcast Feeds

FORUM

Changing FILLFACTOR by Updating sys.indexes

Is is possible to update the FILLFACTOR in the sys.indexes table so that the next rebuild will use t...

Tags
editorial    
fillfactor    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones