Database Reference
In-Depth Information
Figure 5-1.
Logical and physical reads
Let's see what happens when you insert a new row into the index. As you will remember, the data in clustered and
nonclustered indexes is sorted based on the value of the index key, and SQL Server knows the data page in which the
row must be inserted. If the data page has enough free space to accommodate a new row, that would be it. SQL Server
just inserts the new row there. However, if data page does not have enough free space, the following happens:
1.
A new data page and, if needed, a new extent are allocated.
2.
Some data from old data page is moved to the newly allocated page.
3.
Previous and next page pointers are updated in order to maintain a logical sorting order in
the index.
This process called
page split
. Figure
5-2
illustrates the data layout when this happens. It is worth mentioning that a
page split can happen when you update an existing row, thereby increasing its size, and the data page does not have
enough space to accommodate a new, larger version of the row.
Figure 5-2.
Page split and fragmentation
At this point, you have index fragmentation of two kinds: internal and external.
External fragmentation
means that the logical order of the pages does not match their physical order, and/or
logically subsequent pages are not located in the same or adjacent extents. Such fragmentation forces SQL Server
to jump around reading the data from the disk, which makes read-ahead less efficient and increases the number
of physical reads required. Moreover, it increases random disk I/O, which is far less efficient when compared to
sequential I/O in the case of magnetic hard drives.
Internal fragmentation
, on the other hand, means that data pages in the index have free space. As a result, the
index uses more data pages to store data, which increases the number of logical reads during query execution. In
addition, SQL Server uses more memory in buffer pool to cache index pages.
