Database Reference
In-Depth Information
Figure 13-1.
Leaf pages layoutx
Since the index key values in the leaf pages are always sorted, a new index row with a key value of 25 has to
occupy a place between the existing key values 20 and 30. Because the leaf page containing these existing index key
values is full with the four index rows, the new index row will cause the corresponding leaf page to split. A new leaf
page will be assigned to the index, and part of the first leaf page will be moved to this new leaf page so that the new
index key can be inserted in the correct logical order. The links between the index pages will also be updated so that
the pages are logically connected in the order of the index. As shown in Figure
13-2
, the new leaf page, even though
linked to the other pages in the correct logical order, can be physically out of order.
Figure 13-2.
Out-of-order leaf pages
The pages are grouped together in bigger units called
extents
, which can contain eight pages. SQL Server uses
an extent as a physical unit of allocation on the disk. Ideally, the physical order of the extents containing the leaf
pages of an index should be the same as the logical order of the index. This reduces the number of switches required
between extents when retrieving a range of index rows. However, page splits can physically disorder the pages within
the extents, and they can also physically disorder the extents themselves. For example, suppose the first two leaf pages
of the index are in extent 1, and say the third leaf page is in extent 2. If extent 2 contains free space, then the new leaf
page allocated to the index because of the page split will be in extent 2, as shown in Figure
13-3
.
Figure 13-3.
Out-of-order leaf pages distributed across extents
