Sql-server – reorganise index on a sql server 2005 database without performance hit on production server


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.

Best Answer

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.