February 6, 2009 at 10:43 am
What are the pros and cons of rebuilding indedxes ONLINE in sql 2005.
February 6, 2009 at 10:46 am
Pro's - active queries aren't slowed down as the original index is still in place. So, activity can continue as "normal" during the rebuild process (for the most part).
Con's - The online operation uses more space as it actually builds a duplicate index and then drops the original. Additionally, it takes longer so that it doesn't impact the overall processing of the engine quite as significantly as it would without using the ONLINE = ON parameter.
I'm sure others can add to this as well.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 6, 2009 at 10:54 am
David got it covered but I would like to add my two cents.
If you are working in a maintenance window -which means users are not there to bother you- ... rebuild offline.
If you -for any reason- have to rebuild an index on a live database... rebuild online.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply