When running Reorganize Index and Update Statistics in MS SQL Server 2005's maintenance plan, our production web server is timing out. I know that Reorganize Index is an online operation and that is why we chose it over Rebuild Index so we're at a loss. It's run after a Database backup, in the same maintenance plan, but I've read that it shouldn't make a significant impact. Any suggestions?
A:
Do you have enterprise version? Online operation is only available in the enterprise version.
Joakim Backman
Joakim Backman
2009-05-07 06:45:27
Are you sure this is correct? The MSDN page says that reorg index is always online - http://technet.microsoft.com/en-us/library/ms188388(SQL.90).aspx
2009-05-07 14:39:00
Yeah we're running Enterprise edition
Arthur Chaparyan
2009-05-07 20:56:45
hainstech: That is correct - I was missreading the question.
Joakim Backman
2009-05-08 10:54:12
A:
When the web server times out, is it just a plain too much load for how much capacity you have available? What does perfmon show for the avg seconds reads/writes for the physical disk? It may be as simple as throwing money at your disk subsystem.
You may want to try this instead of the reorg:
- create a new index with a different name that is identical to existing index
- drop existing index after new index creation is done
- if you use query hints that specify the index name, you will have to rename the new index to match the old index name