11 Indexes

In this chapter, we will first explore some common mistakes and misconceptions around fragmentation. Understanding index fragmentation can be crucial to a well-performing database. For example, index fragmentation is often considered universally bad, but we will explore how too little internal fragmentation can lead to bad page splits.

We will then turn our attention to index maintenance and look at various mistakes that can lead to an increase in index fragmentation and suboptimal performance. These mistakes range from not rebuilding indexes at all to rebuilding them indiscriminately. We will also assess how index maintenance interacts with updating statistics.

We will explore how indexes impact extract, transform, load (ETL) operations. Specifically, we will explore bulk load operations and discuss how our index strategies are crucial to a performant ETL run. After this, we will take a look at Database Tuning Advisor and think about why it might not be a good idea to rely too heavily on this feature.

Finally, we will look at columnstore indexes, which can add incredible performance improvements to analytical and data warehouse–style workloads. Despite being introduced to SQL Server over a decade ago, they're still not used by many people. We will discuss why this is and why it is a mistake.

11.1 #67 Assuming internal fragmentation is always bad

11.2 #68 Believing that external fragmentation causes problems for all queries

11.3 #69 Reorganizing indexes to fix page density

11.4 #70 Misinterpreting fragmentation statistics

11.5 #71 Not rebuilding indexes

11.6 #72 Rebuilding all indexes indiscriminately

11.7 #73 Updating statistics after rebuilding indexes

11.8 #74 Not optimizing index maintenance for our needs

11.8.1 Considerations for MAXDOP

11.8.2 Considerations for SORT_IN_TEMPDB

11.8.3 Understanding OPTIMIZE_FOR_SEQUENTIAL_KEY

11.9 #75 Not disabling indexes for bulk load

11.10 #76 Relying too heavily on Database Engine Tuning Advisor

11.11 #77 Not using columnstore indexes

Summary