A Methodology for Determining Fill Factors

,

Are Fill Factor settings

important? I believe so. Although I have read from many sources that

mention the default value is generally good enough. I have also read that

you shouldn't change the Fill Factor value unless you know what your

doing.  As with everything else in the world of Database Administration it

is a good idea not to change anything unless you understand what you are

doing.  This is great, but how do you determine the value for a Fill Factor

and know what your doing? I have searched for this answer and was hoping to

find a nice easy formula to set my values. Guess what? I didn't find it.

To understand the Fill

Factor it is beneficial to have a good understanding of clustered and

non-clustered indexes, heap tables, extents, pages, page splits, and the

results from the DBCC SHOWCONTIG command.  If you are not familiar with

these terms then I recommend reading books online or another reputable

source for background. Understanding the makeup of SQL Server’s physical

file architecture is very important.

Basically the Fill Factor

is the percentage of the leaf node page that is used to store the records

when the index is created or rebuilt. We may have all heard this before,

but there is a key point in this statement that is often overlooked.  "When

the index is created or rebuilt."  Once a page split occurs the Fill

Factor is obsolete in the two new pages.  If an index has a Fill Factor of

80 when it is created then most of the pages will be about 80 percent

full.  After one of the pages is filled, a new record is added or changed,

then the page splits into two new pages that are approximately fifty percent

full.  The original Fill Factor setting has no affect on these two new

pages.  If you don't rebuild your indexes then there is a good chance your

indexes have had many page splits, and the Fill Factor setting when it was

created is obsolete unless there have been minimal changes to the number and

content of the records in the table.

Still, what percent should

we set the Fill Factor too?  A low Fill Factor setting will increase the

number of pages required for the index and subsequently cause more reads;

this will hurt performance.  A high Fill Factor could cause a lot of page

splits; this too will hurt performance.  Page splits consume both CPU and

I/O resources, but are a necessary evil.   You may have heard that the

Fill Factor should be high for read only tables and lower for tables with a

lot of changes.  There is some truth to this, but how do you know? If your

environment is anything like mine then figuring out which tables are which

will be a time consuming task.  One of my databases has over 22,000 tables

and you can imagine how long this would take. And indexes on the same table

may fragment at different rates. You want to keep those complaints about

performance at bay so how do you define a 'high' and a 'low' setting?

What I am about to explain

is not an exact science or the right approach in every situation. What this

is, is my "general" rule of thumb that I often follow; similar to putting an

index on foreign keys when creating the physical model.  So, I offer a

caveat.  Try this in a test environment or just work on one or two tables.

Then see how it works for you and the environment you work with. 

The Goals

  • Find a balance between page splitting and index size. A low Fill Factor

    reduces page splitting while increasing the number of pages needed for

    the index.  A high Fill Factor is just the opposite.

  • Keep

    the number of extent switches as low as possible, and the pages

    contiguous.  The Scan Density should be as high as possible for

    each index.  I like 90 percent or higher even though I do not always

    reach this between index rebuilds.

  • Prevent

    spikes in page splits/sec after index rebuilds.  This can literally

    shut down your server.

  • Keep the Average Page Density as high as possible for each

    index.  High Average Page Density values mean fewer reads.

  • Have

    the Average Page Density stay close or increase above the value for

    the Fill Factor setting.  In a perfect world the Average Page

    Density would go from the Fill Factor value to 100 percent between index

    rebuilds.

  • Keep database growth stable or consistent.  You may want to

    monitor is the amount of space used in the files before and after you

    rebuild the indexes.  There's a good chance the amount of space

    used is reduced after the rebuild.

  • Keep performance as consistent as possible between index

    rebuilds.  If I had to mention just one goal, this is it!  The

    previous goals lead up to this one.  I like stability and consistency

    across the board.  This will be one less thing to worry about in the

    event of performance issues.

First Things First, Let's Get Started.

Define a schedule for rebuilding your indexes.  My schedules vary

according to the database. Most of the smaller ones I run a scheduled job

that executes a DBCC DBREINDEX command on all of the user tables once a week

during non-peak hours.  Some databases receive personalized attention at

specified intervals.  This is a very important part of how I am suggesting

to set your Fill Factors.  The Fill Factor setting you choose will become

dependent on the frequency of the index rebuilds

Prepare to obtain some database metrics.  You will be able to add

more to this list, but I wanted to share a few of the more critical ones. 

Through Performance Monitor watch Page Splits/Sec. Remember page splits are

a necessary evil.  You will also want to obtain information from DBCC

SHOWCONTIG.  I have a job that executes once a week that pulls the results

from all of my servers user databases, then loads it into a table.  Of

course I have added columns for Instance and database name, which allows me

to easily query the information.  Run DBCC SHOWCONTIG with the

ALL_INDEXES and TABLERESULTS options.  The TABLERESULTS option allows for

easy reading and manipulation. The ALL_INDEXES option is a necessity

because without it all you will see is the Clustered index or the heap. We

need to see the big picture and treat every index individually. Just

because one index on a table is in good or bad shape it doesn't mean that

all of the indexes on the table are in the same condition.  One index could

be on field with dynamic values while the another index is fairly static.  You should also obtain a list of table names, indexes, and the

OrigFillFactor values in the sysindexes table.

Okay, now grab your database metrics and start your baseline.  The DBCC SHOWCONTIG command will grab some resources so you may wish to do this

when the database is not very busy.  Your database may have a lot of

tables and indexes so you may also wish to break your result sets up

alphabetically or by row count. The results from this command may be a

little intimidating at first. After working with the results for a while

you'll understand the importance of the values. 

Start your DBREINDEX commands per the schedule.  Always be cautious

when executing this command.  It is resource intensive, there is potential

for database corruption if something happens to the server, and in a

counter-intuitive manner the performance in the database may decline.  Say

for example, the Fill Factors are set to 100 and the database is highly

transactional with a lot of inserts. Guess what happens? There will be a

lot of page splits.  I have seen this first hand.  Because the Fill Factor

settings were improperly set, rebuilding the indexes hurt performance for a

couple of days.

After rebuilding the indexes monitor the page splits/sec and see if

it has increased.  Obtain the Page Splits/Sec value midway between index

rebuilds and one more time 24 hours prior to the next rebuild.  You should

obtain this metric 3 times in every cycle. Once after the index rebuild,

another at the midpoint before the next rebuild, and one just before the

rebuild.

Within 24 hours prior to the next rebuild run DBCC SHOWCONTIG and save this

information. 

With the original Fill Factor values and the results from the DBCC

SHOWCONTIG command you are ready to start preparing for changes to the Fill

Factor settings.  With a schedule in place to rebuild the indexes you

can monitor the amount of fragmentation to each index in a given time frame;

assuming the transactional volume is stable. 

I don't bother to adjust indexes that are small. Not small by row count,

but by the number of pages.  This reduces my analysis time and changing

the Fill Factor probably will not have a lot of affect.  For each index

take a look at the Scan Density value.  This is 'generally' what I key off,

but not always.  If the Scan Density is at 24% this is a good sign that the

index was splitting and something needs to change. If the Average Page

Density is at 50% when the Original Fill Factor is at 90 then here too there

are probably some issues. I may also see situations where the Original Fill

Factor is at 80 and the average page density is at 90 percent where I might

be able to increase the Fill Factor now that I know when the indexes will be

rebuilt again.

General Rules/Guidelines for Determining Fill Factor Settings

  • Be consistent with the frequency of index rebuilds.
  • If the index is small then don't adjust the Fill Factor.
  • Monitor and make changes at the index level, not the table level.
  • Keep the Fill Factor values at 0, or between 75 and 100. If any Fill

    Factors need to be set lower than 75 you should be confident that this is

    what is needed.  Which very possibly is the case in which you will keep

    seeing a low Scan Density and a low Average Page Density.  Do some

    discovery work and find out how often the table is read from before setting

    lower Fill Factors.

  • If the Scan Density is at 90 percent or above, leave the Fill Factor

    alone.  At least for the first couple of passes at changing the Fill

    Factors.

  • If the Scan Density is between 60 and 90 percent then bring the Fill

    Factor down with small decrements. Say 2 percent.

  • If the Scan Density is lower than 60 then increase the amount of the

    decrement. Usually I will pick a value that is halfway between the Average

    Page Density and the Original Fill Factor.  For example, if the Original

    Fill Factor is at 100 (or even 0) and the Average Page Density is at 60 then

    I would set the Fill Factor to 80.

  • If the Average Page Density is higher than the Original Fill Factor

    and the Scan Density is close to 100 then raise the value of the Fill

    Factor.  This is great, because you know you can pack the pages a little

    further.  For example, if the Fill Factor is at 80, the Scan Density at 98,

    and the Average Page Density is at 88 then on average the pages filled by 8

    percent just prior to the next index rebuild, and are still fairly

    contiguous; I would increase the Fill Factor, but not bring it higher than

    92 since 8 percent growth brings me to 100 percent and another page split. 

    I would probably bring the value to 88 and watch what happens.

  • Try to avoid drastic changes to the Fill Factor. I intend on taking

    more than one pass at changing the values so I can adjust a little at a

    time.

So there you have it. Some general rules and guidelines to set Fill

Factors. If you rebuild your indexes at regular intervals then you can

determine just how much your indexes are going to fragment.  Use the values

from the DBCC SHOWCONTIG that was executed prior to the next index rebuild

to assist with determining what the new Fill Factor value should be.  By

monitoring you will develop some instincts with page splits and determining

the Fill Factors.  I have yet to find a formula for determining Fill Factor

values, but did establish some procedures.  My general rules are not cut

and dry and I do like to keep the Fill Factor value as high as possible. 

They are basic guidelines with many exceptions and additions.

After you are prepared for obtaining and reviewing the metrics you will be

amazed at just how easy this is.  And just how quickly you can make things

better.  Don't expect to make things perfect. Just be sure to make things

better and work towards the goals.  

Other Points and Considerations

  • Indexes in older, more established databases generally should have

    higher Fill Factors than newer younger databases.  This is easily

    explained.  Many tables grow at a constant rate, not linearly or

    exponentially.  Imagine there is an index that consumes 1000 pages

    initially, grows by 500 pages between index rebuilds, and the Fill Factor is

    set to 90.  This leaves roughly 1000 pages times (.10 * 8096), which equals

    roughly 790 KB of room for growth/changes.  This may be considered a 'high'

    Fill Factor setting which you may want to lower. But once the table has

    10,000 pages with the same Fill Factor of 90 there will be approximately

    7906 KB of room for growth and changes. Once the index has 10,000 pages it

    could potentially be beneficial to raise the value from 90.  The size and

    growth of the index can and ought to be a consideration when setting the

    Fill Factor.

  • Be more concerned with large tables/indexes with low scan densities

    or large drops in average page density than smaller tables/indexes.  It

    takes more page splits to bring the percentages down on a larger index. Remember we're trying to reduce the page splits! 

    A very large index that drops only 2 percent in scan density probably

    experienced more page splits than a small index that dropped 30 percent

    in scan density.

  • Pay attention to more than just the scan density and average page

    density results from the SHOWCONTIG.   There is a lot of relevant

    information here.

  • The Original Fill Factor value in the sysindexes table does not

    represent the Fill Factor value when the index was created, or at least not

    necessarily.  This is the value that was used when the index was last

    created or rebuilt.

  • You may want to consider rebuilding your heap tables too. 
  • After you feel the Fill Factors are properly set plan on revisiting

    them after a period of time.  This is an ongoing maintenance.

Resources

CreateDBREINDEX.sql

GetFileInfo2.sql

Rate

5 (3)

Share

Share

Rate

5 (3)