Optimally setting a FillFactor value

A general question in optimally adjusting a FillFactor setting on an index.
Let's hypothetically say I have a index with a FillFactor = 90.
Again let's hypothetically say it has been 7 days since this index was last
rebuilt. I query master.sys.dm_db_index_physical_stats and this particular
index has an avg_fragmentation_in_percent = 25.0.
I perform the following on the index, changing the FillFactor from 90 to 85:
ALTER INDEX [name of index] ON [name of database].dbo.[name of table]
REBUILD WITH ( FILLFACTOR = 85 )
Let's hypothetically say that it is now 7 days since this index was last
rebuilt with a FillFactor = 85.
Let's also hypothetically say that the same number and exact types of DML
statements occurred on the table where this index resides during this past 7
days as during the 7 day period when the index had a FillFactor = 90.
Now let's say I query master.sys.dm_db_index_physical_stats (after 7 days
with the FillFactor = 85) and this particular index has an
avg_fragmentation_in_percent = 40.0.
In optimally setting the FillFactor for this hypothetical index, from the
above findings I would conclude that a FillFactor = 90 is a more otpimal
setting for the index than a FillFactor = 85. Correct?
The next step in finding the optimal FillFactor setting might be to increase
the FillFactor = 95 and see how the fragmentation compares to when the
FillFactor = 90. Correct?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1The approach you outlined is good and is typical of how one goes about
finding the optimal fill factor. But you really have it backwards from most
real life scenarios. Usually if the fragmentation goes up the fill factor
needs to come down. Fragmentation is mostly due to page splits that happen
when the pages are too full to handle the new rows that need to go on a
particular page. When there isn't room the page must split and that causes
fragmentation. So by decreasing the fill factor you leave more room on the
page and reduce the chance the page will split before the next reindex
occurs. The trick is to balance the splits (or fragmentation) with the
fullness of the pages. If you reduce the fill factor too much you end up
with lots of partially filled pages. That increases the I/O since it
requires more reads to read the same data for pages that are partially full
vs. totally full. Don't get too hung up on the amount of fragmentation. You
can spend a lot of time fine tuning a fill factor for little extra gain.
Don't tweak too many times as close is good enough. For a typical OLTP app
fragmentation is not as critical since you are reading one row at a time and
it takes just as long to read a single page regardless of where it is in the
file. Just keep the pages as full as possible while reducing but not
necessarily eliminating page splits.Where as range scans (frequently found
in OLAP) will be greatly hindered by high values of fragmentation.
--
Andrew J. Kelly SQL MVP
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:76a6c0d915019@.uwe...
>A general question in optimally adjusting a FillFactor setting on an index.
> Let's hypothetically say I have a index with a FillFactor = 90.
> Again let's hypothetically say it has been 7 days since this index was
> last
> rebuilt. I query master.sys.dm_db_index_physical_stats and this particular
> index has an avg_fragmentation_in_percent = 25.0.
> I perform the following on the index, changing the FillFactor from 90 to
> 85:
> ALTER INDEX [name of index] ON [name of database].dbo.[name of table]
> REBUILD WITH ( FILLFACTOR = 85 )
> Let's hypothetically say that it is now 7 days since this index was last
> rebuilt with a FillFactor = 85.
> Let's also hypothetically say that the same number and exact types of DML
> statements occurred on the table where this index resides during this past
> 7
> days as during the 7 day period when the index had a FillFactor = 90.
> Now let's say I query master.sys.dm_db_index_physical_stats (after 7 days
> with the FillFactor = 85) and this particular index has an
> avg_fragmentation_in_percent = 40.0.
> In optimally setting the FillFactor for this hypothetical index, from the
> above findings I would conclude that a FillFactor = 90 is a more otpimal
> setting for the index than a FillFactor = 85. Correct?
> The next step in finding the optimal FillFactor setting might be to
> increase
> the FillFactor = 95 and see how the fragmentation compares to when the
> FillFactor = 90. Correct?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200708/1
>