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.