Link to home
Create AccountLog in
Avatar of wasabi3689
wasabi3689Flag for United States of America

asked on

solution?

Can some one advise me ?please


When I want to run rebuild index for a database ,it will do index rebuilding for all indexes one after the other on particular table.

But, I want to do index rebuilding for all indexes Simultaneously/Parallel.

Please advise.
Avatar of wasabi3689
wasabi3689
Flag of United States of America image

ASKER

I have another question.

Can we rebuild all index online? I mean run this process on live/production database?  it is true that certain version of MS  SQL server like enterprise 2008 above can do it? it will not lock out the tables for accessing when doing this kind rebuilding?
SOLUTION
Avatar of deviprasadg
deviprasadg
Flag of India image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
your links do not say the Online index operations are available only in SQL Server Enterprise, Developer, and Evaluation editions.
ASKER CERTIFIED SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
But, I want to do index rebuilding for all indexes Simultaneously/Parallel.
Then you will have to open multiple connections and run the rebuild separately.  There is no other way.

You should consider why you are rebuilding all your indexes and not just the ones that are fragmented.
We have large amount of index fragmentation. Even we do defrag them, it takes a long time - 1 days to complete. I think, if we can do parallel, it will probably reduce the process time.
My point was that regardless of whether you do it in parallel or not, you should only re-index/re-organize those tables that are fragmented, by whatever measure you deem.  But quite honestly you probably won't save much time doing it in parallel and probably your biggest problem will be your Transaction Log growing very big, very fast.
if so, the question is how to reduce the transaction log? Do you have website support you say " won't save much time doing it in parallel "?
if so, the question is how to reduce the transaction log?
Actually the question should be how do I prevent the Transaction Log from growing?  And the answer is when using Full Recovery Model backup the Transaction Log more frequently.

Do you have website support you say " won't save much time doing it in parallel "?
I am afraid not.
my database is simple recovery mode
my database is simple recovery mode
Than you don't have to worry about Transaction Log backups and just make sure the Transaction Log is large enough for your situation.