Tweaking the Fill Factor to reduce fragmentation

Question

I have an non-clustered index that has ~1000 pages, 95% fragmentation and a FillFactor of '0'.

I rebuilt the index on Sunday. A week later, the index has 95% (or so) fragmentation again. Is this an indication that I need to change the FillFactor to say 80-90%?

Should I be tweaking the fill factor each week to see if I can reduce the fragmentation from week to week? Is there a target level I shoot be shooting for?

Thank you.

Answer 1

I would consider tweaking your fill factor for sure. If it's fragmenting quickly, I'm guessing it's a write-heavy index, which can gain substantial performance benefits from a fill factor.

Determing optimal fill factors is pretty much a trial-and-error process though, and more of an art than a science. From Tips for Rebuilding Indexes by Brad McGehee:

So what is the ideal fill factor? It depends on the ratio of reads to writes that your application makes to your SQL Server tables. As a rule of thumb, follow these guidelines:

* Low Update Tables (100-1 read to write ratio): 100% fill factor
* High Update Tables (where writes exceed reads): 50%-70% fill factor
* Everything In-Between: 80%-90% fill factor.