I'm researching defragmenting databases and it seems the following SQL statement is what I'm looking for:

ALTER INDEX ALL ON mytablename

When I pull the info from sys.dm_db_index_physical_stats, I see the percents are very high - 70, 80 and 90 percent (!). So that tells me I want a REBUILD, and not a REORGANIZE.

I have a few question before executing these REBUILDS.

  1. Can it be ran while processes are using the database? (ONLINE = ON tells me yes, but I want to confirm it won't crash anything.) Or is it better to run when it's not in use?
  2. I read REBUILD makes things run slower. Is that just while the indexes are being rebuilt? (or forever after)
  3. How long will it take to rebuild all indexes / or rather each one?
  4. Are there any side effects or other info that I need to be aware of? This is production/live database.

EDIT: And finally, what is the best way to go about rebuilding it? Looping through all the objects with a percentage of greater than 30? Or?

Thank you!

  • 4
    Note that online index operations require the Enterprise Edition of SQL Server. Commented Jan 18, 2012 at 19:05
  • Thank you. Yes, we have the Enterprise edition.
    – Becky
    Commented Jan 18, 2012 at 19:27
  • The only way to tell how long it will take is copy copy the database to a similar server (same hardware config) and run the rebuild there.
    – datagod
    Commented Jan 18, 2012 at 20:31

2 Answers 2

  1. Yes you can rebuild while the database is online with active users. It's definitely better to do it off-peak if possible.
  2. During the rebuild your queries will run slower, mostly due to the I/O overhead of rebuilding the index. How noticeable this is depends on the specifics of your system. The performance penalty is only during the rebuild - not forever after.
  3. Again, the duration of the rebuild depends on your specific setup. You should try running this on an equivalent development or staging server.
  4. No other associated side-effects. Once the rebuild is complete you'll be back up and running as usual.
  • 2
    For point 4, he should be aware the space taken by his database on disk will increase substantially as it will need to accommodate two versions of the rebuilding index. Essentially it will add slack space equal in size to his largest index.
    – JNK
    Commented Jan 18, 2012 at 19:21
  • @JNK Yes, good point. Thanks for catching that.
    – Yuck
    Commented Jan 18, 2012 at 19:21

I would also strongly recommend looking at Ola's Index Maintenance scripts. No reason to recreate the wheel. Its a very flexible script and many DBA's in the community use it and recommend it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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