Chapter 10. Maintaining indexes
In the previous chapter, I explained how SQL Server indexes are built, and broadly hinted at how SQL Server uses them. Indexes will play a big part in many upcoming chapters, as they’re one of the biggest performance-related items that an administrator can potentially play with. In keeping with this book’s overall theme, though, I’m assuming that your databases are already indexed using whatever indexes the database application developer figured would work well.
But indexes, like cars, don’t work very well, for very long, unless they’re properly maintained.
The biggest problem—the only operational problem—that indexes run into is fragmentation. Remember that an index is an ordered reference to a particular table’s data. The index takes all the values from a designated key column, like customer name or product stock number, and lists those values in a specific order. The idea is that it’s much easier to find a piece of data when it’s already sorted: you can seek to the section of the index you need, and find the item you want. That’s what an index’s B-tree structure helps to facilitate.
Note
I use the word seek specifically because it’s the term SQL Server uses to describe a quick lookup in a B-tree. Another term is scan, wherein SQL Server literally starts at the top of the list and reads its way through, one item at a time. Seeks are almost always faster than a scan, except on a very small table.