Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
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
add comment

migrated from stackoverflow.com Jan 18 '12 at 20:28

This question came from our site for professional and enthusiast programmers.

2 Answers

up vote 6 down vote accepted
  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.
share|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
1  
@Yuck: Thank you for the info! Very helpful. Any thought on the FILLFACTOR? –  Becky Jan 18 '12 at 19:27
1  
@JNK: Thank you for pointing that out! Where can I find the size of my largest index? I need to make sure we have the space available. –  Becky Jan 18 '12 at 19:27
1  
@Becky take a look at Michelle Ufford's index maintenance script and see if it would be applicable for your environment. It's a very comprehensive index defragmentation script that has been through several revisions, works well to keep your indexes maintained: sqlfool.com/2011/06/index-defrag-script-v4-1 –  beargle Jan 18 '12 at 21:55
show 4 more comments

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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