8

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

ALTER INDEX ALL ON mytablename
REBUILD WITH(ONLINE = ON)

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. – Joe Stefanelli Jan 18 '12 at 19:05
  • Thank you. Yes, we have the Enterprise edition. – Becky Jan 18 '12 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 Jan 18 '12 at 20:31
7
  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.
| improve this answer | |
  • 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 Jan 18 '12 at 19:21
  • @JNK Yes, good point. Thanks for catching that. – Yuck Jan 18 '12 at 19:21
4

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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