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.
Sql-server – reorganise index on a sql server 2005 database without performance hit on production server
indexperformancesql-server-2005
Related Question
- Sql-server – NHibernate Parameter Sniffing, SQL Server 2005 vs SQL Server 2008
- Sql-server – SQL Server: Duplicate Index Question
- Sql-server – SQL Server 2008 – Question about index behaviour
- Sql-server – Online index rebuild snapshot location and impact of filling index drive
- SQL Server – Can Updating All Statistics Separately Cause Bad Performance?
- MySQL – Force index degraded performance over time
- SQL Server – How to Investigate Wrong Index Selection
Best Answer
Correct: online index rebuilds are only supported in the Enterprise edition engine.
ALTER INDEX ... REORGANIZEis 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
REORGANIZEis 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.