Sql-server – Defragmentation – Rebuilding Indexes SQL Server 2005

fragmentationindexmaintenancesql server

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!

Best Answer

  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.
Related Question