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.