How to choose a good index fill factor

Q: I’ve been reading all the recent blog posts here about how some index keys can lead to fragmentation (and we have some that are GUIDs unfortunately). I’d like to reduce the amount of fragmentation that occurs but I can’t change the database schema at all. Should I set a default fill factor for the instance?

A: This answer ties in nicely with my previous blog post on Fragmentation from using snapshot isolation where one of the workarounds is to use an index fill factor.

You are right that making use of index fill factors is a great way to avoid fragmentation if you’re unable to change the indexes themselves, but I would strongly advise against setting an instance-wide fill factor using sp_configure.

As background for other readers, an index fill factor instructs the Storage Engine to leave some free space in leaf-level index pages during index build or rebuild operations (note that the fill factor is NOT maintained during regular insert/update/delete operations).

The default fill factor is 100, which instructs the Storage Engine to make the index leaf level pages 100% full – in other words leaving no space. This means that pages do not have space to allow random record inserts (or records increasing in size) and so page splits can occur, causing fragmentation.

The tricky part about setting a fill factor is determining what number to use for each index. If you set an instance-wide fill factor there will likely be many indexes that do not need a fill factor set and so they will contain a lot of wasted, empty space. This can be especially true of clustered indexes where the clustering key and access pattern do not cause fragmentation.

For those indexes where fragmentation is a problem, and your only recourse is to use a fill factor combined with regular index maintenance, you need to find a balance between how low to set the fill factor and how often you can rebuild the index to reset the free space in the index leaf-level pages. So what’s the secret?

The unfortunate answer is that there isn’t a secret to which number to pick, but there is a pretty simple methodology you can use to help you set an appropriate fill factor for each of your problem indexes.

I usually recommend picking a fill factor value and putting it in production – and I often pick 70% as the starting value. Monitor the fragmentation over a period of a week or two and then decide whether to tweak the fill factor value up or down, or increase/decrease the frequency of index maintenance.

You can monitor the fragmentation using the sys.dm_db_index_physical_stats DMV which from SQL Server 2005 onwards replaces DBCC SHOWCONTIG (that I wrote for SQL Server 2000). Just be careful how you run the DMV as it can be really expensive – see my blog post Inside sys.dm_db_index_physical_stats for a *really* in-depth look at the DMV and how it works.

For some of my clients, I’ve helped them set index fill factors as low as 50%, as the extra space usage is preferable to the run-time cost of performing page splits.

Your mileage will vary – just don’t fall into the trap of setting a default fill factor for the whole instance – this invariably leads to far too much wasted space.

Discuss this Blog Entry 6

mark (not verified)
on Sep 30, 2010
what does fillfactor 0 mean? does it maintain your previous settings or do you need to know what your current setting is before you reindex?
Tahir (not verified)
on Oct 2, 2010
Does Fill Factor impact read / write operation. I think when we set Fill factor to 50% or so it will slow down read operation. similarly if we set it to 100% it will slow down the write operatoin. How to get optimal performance through fill factor both for read / write operation.

Thank you.



on Oct 1, 2010
Looks like this is a endless task, since this depends on the data beeing stored and the data can change everyday, in some cases more often then that, is it possible to implement some job to automatically respond to fragmentation problems?
PaulRandal (not verified)
on Oct 1, 2010
Yes, of course - no-one does this manually. At the lowest end, the maintenance plan wizard can help you with this, or you can download a script to customize for yourself. Google Ola Holangren and get his latest script.
PaulRandal (not verified)
on Oct 1, 2010
If you don't specify a fill factor, the previously used fill factor for that index (or the default, if this is the first index operation) will be used. If you specify 0 (or 100) that says to leave zero free space in the index leaf-level pages.
PaulRandal (not verified)
on Oct 3, 2010
Yes = and that's part of the trade off you need to make for your particular situation. There's no right answer - experiment.

Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×