Index Fragmentation

Abstract

As explained in Chapter 8, rowstore index column values are stored in the leaf pages of an index’s B-tree structure. Columnstore indexes are also stored in pages, but not within a 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, whereas a columnstore has the data pivoted into columns and then compressed, again with the intent of assisting in data retrieval. 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. A similar situation occurs with the columnstore as data is moved from the deltastore to the segmented storage areas. Both these situations can lead to performance degradation.