Aside from page splits,
logical fragmentation can also be introduced by deleting data
(resulting in free space within a page, dropping the page density).
What can cause a page split ?
A page split is what occurs to a database page
when new data of some kind needs to fit a page,
but there is not enough room on the page
to acommodate all the data needed to be placed in the page.
Page splits do NOT occur on a heap,
only on indexes (clustered or non-clustered).
What a page split is involves a complicated discussion,
because it is a fairly complicated operation
- for the sake of ease of description,
think of it as a single page being split in half,
with 1/2 of the rows on the page moving to a newly allocated page,
and the other 1/2 of the rows remaining where they are.
Also, though a page split can occur on any index page (root, intermediate, leaf).
2 operations that can commonly cause a page split include:
1. Inserting a new record onto a full/nearly full page
- this will occur when you have an index (clustered or non-clustered) on a column(s)
and the new record to be inserted
is not positioned at the end of the existing index chain
(i.e. it needs to be inserted somewhere in the "middle" of the existing pages).
This will never occur
when the index key columns are ever-increasing
(i.e. identity values,
increasing date/time values (assuming none occur at the same time), etc.).
2. Updating an existing record's variable-length column(s) to larger values on pages
that don't have enough free-space to accommodate the increased sizes.
This can only occur with variable-length values,
since fixed-length columns
are always stored with a fixed storage footprint.
To be accurate,
this type of update would be applied by the SQL engine
as first a delete of the existing row followed by an insert of the new data.
As you can deduce,
a page split introduces multiple types of logical fragmentation at once:
a)
since the 2 pages involved in the page split operation
hold only 1/2 the records as originally fit on a single page,
the 2 pages are only about 50% full each, and
b)
since a new page had to be allocated to hold 1/2 the records,
there's a very good chance
that this new page is not physically contiguous
with the logically ordered previous/next pages in the linked list.
Therefore, you end up with pages that have a lower page density,
are not contiguous, and hence not properly ordered.
Not very desirable indeed!
An additional contributor to logical fragmentation is ironically something
that is typically used to try and avoid it,
but is many times used incorrectly - fill-factor.
Setting a fill-factor for an index
will limit how full a page is allowed to become as a percentage,
thereby attempting to leave a certain amount of free-space
in every page to ward-off eventual page-splits.
Naturally, if this is used correctly and appropriately,
it can be a very desirable feature - used incorrectly
however, it can cause bloated page-counts
and an overall inefficiency in that page density for given indexes.
Let's see what we can do to try and display logical fragmentation in a simple set of diagrams:
Source:
http://www.mssqltips.com/sqlservertip/2263/sql-server-fragmentation-what-causes-it-part-3-of-9/