Database Reference
In-Depth Information
Figure
32-10
shows an example of a leaf-level index page with an insert delta record.
Figure 32-10.
Leaf-level index page with an insert delta record
SQL Server needs to traverse and analyze all delta records when accessing an index page. As you can guess,
a long chain of delta records affects performance. When this is the case, SQL Server consolidates delta records and
rebuilds an index page creating a new one. The newly created page will have the same PID and replace the old page,
which will be marked for garbage collection. Replacement of the page is accomplished by changing a pointer in the
mapping table. SQL Server does not need to change internal pages because they use the mapping table to reference
leaf-level pages.
The process of rebuilding is triggered at the moment a new delta record is created for pages that already have 16
delta records in a chain. The action described by the delta record, which triggers the rebuild, will be incorporated into
the newly created page.
Two other processes can create new or delete existing index pages in addition to delta record consolidation.
The first process,
page splitting
, occurs when a page does not have enough free space to accommodate a new data row.
Let's look at this situation in more detail.
Figure
32-11
shows the internal and leaf pages of the range index. Let's assume that one of the sessions wants to
insert a row with a key of value
Bob.
Figure 32-11.
Page splitting: Initial state
