Professional SQL Server™ 2005 Programming

8.6. Maintaining Your Indexes

As developers, we often tend to forget about our product after it goes out the door. For many kinds of software, that's something you can get away with just fine — you ship it and then you move on to the next product or next release. However, with database-driven projects, it's virtually impossible to get away with. You need to take responsibility for the product well beyond the delivery date.

Please don't take me to mean that you have to go serve a stint in the tech support department — I'm actually talking about something even more important: maintenance planning.

There are really two issues to be dealt with in terms of the maintenance of indexes:

• Page splits

• Fragmentation

Both are related to page density and, while the symptoms are substantially different, the troubleshooting tool is the same, as is the cure.

8.6.1. Fragmentation

We've already talked about page splits quite a bit, but we haven't really touched on fragmentation. I'm not talking about the fragmentation that you may have heard of with your O/S files and the defrag tool you use, because that won't help with database fragmentation.

Fragmentation happens when your database grows, pages split, and then data is eventually deleted. While the B-Tree mechanism is really not that bad at keeping things balanced from a growth point of view, it doesn't really have a whole lot to offer as you delete data. Eventually, you may get down to a situation where you have one record on this page, a few ...