Given that the primary contributor to logical fragmentation is page splitting,
the key to avoiding logical fragmentation
is to do everything possible to avoid page-splitting.
This can be done in a couple of different ways:
(1).
Choose index keys that are either ever-increasing values or ever-decreasing values.
In this type of index setup,
newly inserted records
will always be placed at either the beginning or end of the logical page-chain,
and hence lead to very few (if any) page-splitting as a result of insert operations.
You would still see page splitting at the root/intermediate level of the index,
but rarely (if ever) at the leaf level of the index.
If you are using GUID values for index keys,
with SQL Server 2005,
consider using the new function "newsequentialid()"
to generate ever-increasing GUID values
instead of random location values.
(2).
Choose static index keys.
If an index key value never changes,
it will never have to be moved from the position it is placed in originally
as a result of an update to the key values.
If an index key is non-static,
when the value(s) changes,
the record is nearly guaranteed to require being moved to
the appropriate new logical position within the index,
thereby causing 2 potential side-effects:
(a)
the page that the record was originally on will have a lower density
(at least for some time)
due to the empty space left by the record moving to a new location
- and
(b)
the new page that the record is moved into
will likely incur a page-split operation
(assuming the new page is already at or near full capacity).
(3).
Use an appropriate Fill Factor setting.
Notice quickly that it didn't say to "use a fillfactor"...
But said use an appropriate setting.
In the majority of scenarios,
the default fillfactor (zero...equivalent to 100) setting is appropriate.
Many times,
fillfactor settings are used without gathering any statistical information
to back-up the reason for choosing the setting,
which leads to wasted space on pages,
a bloated buffer pool, and low page densities.
If you've used/are using fillfactor settings
that are not the default,
are you really sure you're using the appropriate value?
What tests/analysis did you do to determine the value you are using?
If the page density values for indexes
that have a non-default fillfactor value set are consistently close to the fillfactor value,
that's a good starting sign
that the appropriate fillfactor is probably not in use.
On the other hand,
if you are using a default fillfactor setting,
and your indexes are consistently showing high logical fragmentation values
and low page densities
after an appropriate amount of activity
(appropriate depends on your environment),
that's a good starting sign that a non-default fillfactor might help.
* * *
Remember
that using a non-default fillfactor
is in effect introducing a form of logical fragmentation up front
(building the index with a lower-than-optimal page density)
to help avoid heavy amounts of logical fragmentation in the long run.
Remember too
that all forms of fragmentation other than low page density
only impact performance on transfers of data
from disk to memory and vice versa.
Therefore,
if the benefit your workload gets from having contiguous pages on disk
and thereby being able to perform faster sequential reads
(traditional OLAP type systems)
outweighs the impact of having lower-than-optimal page densities in cache,
a lower fillfactor value is going to be more effective
than scenarios where sequential reads do not occur too frequently (traditional OLTP type systems).
* * *
There are some scenarios where it generally makes absolutely no sense to have a non-default fillfactor setting:
(1). Read-only workloads.
Why would you have a fillfactor here?
There is absolutely no good reason for it
- if there are no writes occurring,
there's no potential for any type of fragmentation to be introduced.
(2). Indexes with a ever-increasing or ever-decreasing value.
Given that inserts will always be positioned at the beginning or end of the index,
where would a fillfactor help?
The only scenarios this would help would be when this applies
but the index keys are not static (rare)
and they would be updated a significant amount of the time,
or a significant portion would be updated at a given time (rare again),
or if variable length columns within the records
are updated from small values to large values frequently
(thereby causing a growth of size in the record,
potentially causing it to need to move to another page).
These scenarios are quite rare when using these types of keys, but they do happen.
(3). Indexes on identity values.
This is basically the same as #2.
Source:
http://www.mssqltips.com/sqlservertip/2264/sql-server-fragmentation-how-to-avoid-it-part-4-of-9/