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