What’s the difference between reorganizing and rebuilding an index?

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.

About Kathi Kellenberger

I am the editor of the online journal Simple Talk. I love talking about SQL Server to anyone who will listen, just ask my seven year old granddaughter. I love to write and teach. I am so humbled and honored to say I am a Data Platform MVP, a volunteer with LaunchCode and co-leader of PASS Women in Technology Virtual Group.
This entry was posted in SQL Server Administration. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s