I was teaching a group of women in St. Louis about database maintenance. When I began talking about index maintenance, I used an interesting analogy to explain the difference between reorganizing and rebuilding of an index. The technical answer is that the reorg only removes fragmentation from the leaf level while the rebuild completely recreates the index.

Index maintenance can be automated in one of two ways. Either you can use the built-in Maintenance Wizard, or you can use a scripted solution. Most experienced DBAs use scripts for maintenance instead of the wizard, but the wizard is a good choice in some cases. When using the wizard, you must choose between reorg and rebuild, and the choice affects all indexes in the database. Scripting allows tables that are not fragmented to be skipped, and the action to be performed can be controlled by the fragmentation level.

One of the mistakes I have seen with wizard generated maintenance plans is selecting both reorg and rebuild. Imagine that you have a wall in a room that hasn’t been painted for quite some time. Maybe the wall is dirty, but you can fix that by just painting. If the wall is in really bad shape, you may need to knock the wall down and rebuild it. There is no point in painting a wall right before you rebuild it.

If you do intend to use the Maintenance Wizard for index maintenance, I suggest that you Rebuild. If your databases are larger than a couple of gigabytes, you should probably consider a scripted solution instead. You don’t even need to create the solution yourself because there are plenty of free scripts available. Check out Ola Hallengren’s solution that has been used by thousands of databases administrators around the world.