February 21, 2012 at 10:32 am
What would be the proper way for me to rebuild indexes with out affecting the table access? Should I disable the index first then rebuild it or when issues the alter index statement should I add ONLINE=OFF? I have several indexes that are over 40 % fragemented and I want to rebuild them but I can't have it deny reads and writes to the database.
Also just for my understanding a reorganize of the index is always online and doesn't affect access to the table. Is this a correct statement?
sorry for such a simple question but my research using msdn has been rather dismal.
February 21, 2012 at 10:37 am
Reorganise is an online operation, if you're on Enterprise Edition you can rebuild the indexes online
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2012 at 10:41 am
Ok but what happens if I do a rebuild. Will it put a lock on the table? All of my instances are standard. If it does put a lock on the table is there anyway around it like first disabling the index?
February 21, 2012 at 10:43 am
If you're on standard edition, rebuilding indexes is an offline operation always, for a nonclustered index rebuild the table will be read-only for the duration, for a clustered index rebuild the table will be completely unavailable for the duration.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 21, 2012 at 10:45 am
Great. Thanks for the info. This won't only help for rebuilding the indexes in production but also for my studies on the 70-432.
Again many thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply