I'm studying SQL Server and now am learning how to Reorganize and Rebuild Indexes. I've read already at msdn.microsoft, but It's not clear for me yet. Please advise what is the best way when I have to use it. And what are the pros and cons of using it?
Rebuild: An index ‘rebuild’ creates a fresh, sparkling new structure for the index. If the index is disabled, rebuilding brings it back to life. You can apply a new fillfactor when you rebuild an index. If you cancel a rebuild operation midway, it must roll back (and if it’s being done offline, that can take a while).
Reorganize: This option is more lightweight. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).
Should I use
If you have a regularly scheduled downtime every weekend, you’re probably fine with straight up index rebuilds, even if you have Standard Edition. Single threaded offline index maintenance may not be the hottest thing in the world, but hey, if you’ve got time for it then embrace the simplicity.
If you have Enterprise Edition, embrace parallel index rebuilds– and use the ONLINE option for indexes that allow it if people need to access the database during your maintenance window.
If you have database mirroring or AlwaysOn Availability Groups, tread with caution– particularly with rebuilds. It’s easy to generate a ton of IO with index maintenance, and it could mean putting your secondaries or mirror so far behind that they can’t catch up.
Read Kendra's article for other important information you should take into account.
Rebuilding ends up with a superior result. Use it whenever possible.
Use reorganize only when you're forced to, such as
You don't have Enterprise edition features like online rebuilds, and aren't allowed to lock the index for long enough to rebuild it offline.
- Even during off hours or on weekends
You aren't allowed to put a rebuild's load on the system for long enough to finish, and so you run it in "chunks".
- This is not going to get good results over time.