SQL Server 2017 Query Performance Tuning: Troubleshoot and Optimize Query Performance

© Grant Fritchey 2018

Grant FritcheySQL Server 2017 Query Performance Tuninghttps://doi.org/10.1007/978-1-4842-3888-2_14

14. Index Fragmentation

Grant Fritchey¹ 

(1)

Grafton, Massachusetts, USA

 

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, ...