As explained in Chapter 4, 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
The overhead costs associated with fragmentation
How to analyze the amount of fragmentation ...