Sql-server – Viable strategies for rebuilding indexes online in SQL Server 2008 R2 Standard edition

index-tuningsql serversql-server-2008

We use SQL Server 2008 R2 Standard edition (we can't afford Enterprise edition on all our production database servers) and have to periodically maintain the critical indexes (rebuilding/reorganizing) in our production servers. The problem is that since we are using Standard edition we don't have access to the rebuild index online option. If we don't limit access to the database while these critical indexes are rebuilt, we get many timeout errors because the index is essentially disabled during rebuild. I've used scripts such as Ola Hallengren's Index Optimize script, however this doesn't seem to solve my problem.

What I'm thinking to do to work around this problem is instead of rebuilding an index when an index is fragmented, create a separate index copy (i.e. IX_some_name_temp), once that is done, drop the original fragmented index and finally rename the copy index back to the original name. What I'm hoping is that while the new index is being built Sql Server can use the original (somewhat fragmented index), then after the new temp index is built it can start using that one, then we drop the fragmented index and rename and we're back to the original state for the next time we have to run our scheduled job.

My question is, does this approach make sense? Will sql server be able to utilize my new temp index copy while I'm dropping the original fragmented index?
Any tips or possibly other strategies are appreciated.

Best Answer

Assuming these are non clustered indexes then the CREATE INDEX topic on BOL discussing offline operations indicates this approach should only partially work in that it will allow read only access to the table but will still block updates. The relevant section is below (emphasis mine)

Table locks are applied for the duration of the index operation. An offline index operation that creates, rebuilds, or drops a clustered index, or rebuilds or drops a nonclustered index, acquires a Schema modification (Sch-M) lock on the table. This prevents all user access to the underlying table for the duration of the operation. An offline index operation that creates a nonclustered index acquires a Shared (S) lock on the table. This prevents updates to the underlying table but allows read operations, such as SELECT statements.