Reorganize online Sql server standard edition 2005 (64-bit)

Question

Does the sql server performance degrades when Reorganize a huge table online with limited activity.?
IS this valid online activity on Standard edition.?

Thanks
Naga

Answer 1

How big is table in size?

Answer 2

You may experience a performance hit when re-organising a table.  It all depends on how fragmented your table is, and how much I/O activity is on the server.  A reorganise is a very write-intensive process, so the I/O subsystem performance will be the most critical factor.

ALTER INDEX REORGANIZE is a single threaded operation, so you should have plenty of CPU spare for other queries.  It is always an online operation, regardless of Standard Edition or Enterprise Edition.

The best part about REORGANIZE is that you can stop it at any time, and you haven't lost any of the effect (as opposed to rebuilding an index - if you cancel the task, you lose all the work that was done).  So, my suggestion to you is to try the REORGANIZE, monitor the performance on the server, and stop the reorganise if you feel performance is negatively affected.

Answer 3

Hi Naga,

The online operation is specific for ENTERPRISE only in case of REBUILD index not for Reorganise .
I would typically run this kind of reorganize during off-prod hours and will not take any risk of application load accessing the load.
To find out the fragmentation in the indexes , use sys.dm_db_index_physical_stats DMV and see the avg frag percent and finally decide whether or not to reorg / rebuild.

---

Thanks, Leks