Database Reference
In-Depth Information
Chapter 13
Index Fragmentation
As explained in Chapter 8, index column values are stored in the leaf pages of an index's B-tree structure. When you
create an index (clustered or nonclustered) on a table, the cost of data retrieval is reduced by properly ordering the
leaf pages of the index and the rows within the leaf pages. In an OLTP database, data changes continually, causing
fragmentation of the indexes. As a result, the number of reads required to return the same number of rows increases
over time.
In this chapter, I cover the following topics:
•
The causes of index fragmentation, including an analysis of page splits caused by
INSERT
and
UPDATE
statements
•
The overhead costs associated with fragmentation
•
How to analyze the amount of fragmentation
•
Techniques used to resolve fragmentation
•
The significance of the fill factor in helping to control fragmentation
•
How to automate the fragmentation analysis process
Causes of Fragmentation
Fragmentation occurs when data is modified in a table. When you insert or update data in a table (via
INSERT
or
UPDATE
), the table's corresponding clustered indexes and the affected nonclustered indexes are modified. This can
cause an index leaf page split if the modification to an index can't be accommodated in the same page. A new leaf
page will then be added that contains part of the original page and maintains the logical order of the rows in the index
key. Although the new leaf page maintains the
logical
order of the data rows in the original page, this new page usually
won't be
physically
adjacent to the original page on the disk. Or, put a slightly different way, the logical key order of the
index doesn't match the physical order within the file.
For example, suppose an index has nine key values (or index rows) and the average size of the index rows allows
a maximum of four index rows in a leaf page. As explained in Chapter 8, the 8KB leaf pages are connected to the
previous and next leaf pages to maintain the logical order of the index. Figure
13-1
illustrates the layout of the leaf
pages for the index.
