Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

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.

share|improve this question
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

migrated from stackoverflow.com Aug 1 '12 at 13:55

4 Answers

up vote 2 down vote accepted

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.

share|improve this answer
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
up vote 0 down vote

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.

share|improve this answer
up vote 0 down vote

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

share|improve this answer
up vote -1 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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