Index maintenance

While index maintenance is more of a database administration topic than a developer topic, it’s worth discussing the importance of index maintenance. As we discussed in the section on index structure, over time, INSERT, UPDATE, and DELETE operations can cause an index to become fragmented. Once the data is in memory, fragmentation doesn’t cause a noticeable performance issue, so the main concern is I/O. The SQL Database Engine has a few I/O optimizations, such as the readahead mechanism that’s used when scanning an index, that rely on the data being stored contiguously. When the data is fragmented, I/O may not be as efficient.

Another side effect of fragmentation is lower page density. A page is the smallest unit of I/O in the SQL Database Engine, so an index that contains a lot of partially empty pages will generate a lot more I/O than necessary. If the pages are full, it will take fewer of them to store the same amount of data. This is a...