Professional SQL Server™ 2005 Programming

24.3. Index Maintenance

Back in Chapter 8, we talked about the issue of how indexes can become fragmented. This can become a major impediment to the performance of your database over time, and it's something that you need to have a strategy in place to deal with. Fortunately, SQL Server has commands that will reorganize your data and indexes to clean things up. Couple that with the job scheduling that we've already learned about, and you can automate routine defragmentation.

The commands that have to do with index defragmentation were altered fairly radically with this release of SQL Server. The workhorse of the old days was an option in what was sometimes known as the Database Consistency Checker — or DBCC. I see DBCC referred to these days as Database Console Command, but either way, what we're talking about is DBCC (specifically, DBCC INDEXDEFRAG and, to a lesser extent, DBCC DBREINDEX for our index needs). This has been replaced with the new ALTER INDEX command.

ALTER INDEX is the new workhorse of database maintenance. It is simultaneously much easier and slightly harder than DBCC used to be. Let's take a look at this one real quick, and then at how to get it scheduled.

24.3.1. ALTER INDEX

The command ALTER INDEX is somewhat deceptive in what it does. Up until now, ALTER commands have always been about changing the definition of our object. We ALTER tables to add or disable constraints and columns, for example. ALTER INDEX is different — it is all about maintenance and zero ...