I would have preferred to run online index rebuild instead but our production sql server (2005) is standard edition. Hence I think I am left with reorganising index and update statistics after that. Please correct me if I am wrong. But I am wondering if I can do this without any performance hit or not.

  • Do you have no maintenance window to do this sort of operation during? – Thomas Stringer Aug 1 '12 at 13:50
  • @shark I would call for a maintenance window if necessary, but I am wondering if I can do without one. – Sekhar Aug 1 '12 at 14:13
  • 2
    Reorganize is online and much lighter than an online rebuild. – Remus Rusanu Aug 1 '12 at 14:20
  • I love reorg, rebuild is way overused. Make sure ALLOW_PAGE_LOCKS = ON or you can't reorg. – Eric Higgins Aug 1 '12 at 21:22

Correct: online index rebuilds are only supported in the Enterprise edition engine.

ALTER INDEX ... REORGANIZE is always an online operation, regardless of your edition of SQL Server, even on 2005.

Doing this while the system is in operation will certainly affect performance as far as physical disk workload, but it should not affect read or modification operations in terms of locking. See the link above for more details.

Since doing a REORGANIZE is online, you can choose to schedule it more frequently, perhaps even taking advantage of periods of slower activity. The key idea would be to do it more frequently, but do less work each time, as compared to, say, doing it once/day or once/week.

  • Thanks for that Jon, I will start with Reorganise at slower activity period and see how it goes. – Sekhar Aug 2 '12 at 8:34

Definitely the index re-organising is resource intensive process and you will experience the performance issues.

As this process will have to reorder all the leaf nodes of the index B-Tree.

So my suggestion is to try re-organising the index during the idle server time and also you can try this for a while and if its causing so much of performance issues, you can immediately stop this action. As there is a one good thing about this reorganising action is we can stop it at any point and the work done so far will be saved.


You're correct that you can't do an online. You're correct that you'll have to manually update stats if you go the "reorg" route. It's impossible to say if doing a "reorg" instead of a "rebuild" will be adequate to maintain acceptable user response/performance.

I'd start with the reorg and go from there...


Maybe this misses your point, but could you recreate the index with a new name, drop the old index, and rename the new kne into its place? You'd still have the performance hits from the reads to create the index, but statements running in the meantime can continue to use the old index until the new one I ready.

  • Why has this been voted down? Is this a viable option when a complete rebuild is needed, but you don't have access to ONLINE due to running standard edition? – Torbjørn Aug 1 '13 at 10:26
  • I'm unclear why this was voted down as well - it's something I've used a number of times, and if it's a bad idea, I'd like to know why. – SqlRyan Aug 1 '13 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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