Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 7 down vote accepted

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.

share|improve this answer
    
Thanks for that, very helpful to know that my idea doesn't sound like it would do any good. –  Matt Palmerlee Oct 2 '12 at 23:15
add comment

I think that you have to upgrade to Enterprise Edition and do online rebuilds or accept that there will be some timeouts during index maintenance.

share|improve this answer
    
Welcome to S.E. Ola - you get quoted on here all the time! –  RThomas Oct 3 '12 at 16:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.