4

I currently have a maintenance plan that is reorganizing indexes everyday and I would like to know if you reorganize an index everyday would you ever need to rebuild the index? If so, why?

8

This should largely be a Google question and might get marked, but I'll add an answer just in case it helps others. Please see a blog post by the Brent Ozar crew: http://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/

First off: ‘Reorganize’ and ‘Rebuild’ are two different operations that each reduce fragmentation in an index. They work differently toward the same end. You don’t need to run both against the same index. (I sometimes find that people are doing both against every index in a maintenance plan. That’s just double the work and NOT double the fun.)

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).

Do you need to reorganize your indexes every day? Most likely not and you're going to increase disk pressure with higher wear and tear. A solution like Ola Hallagren's scripts help out often but make sure you don't reindex during business hours and take database size into account.

https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.