patternsqlMinor

Defragmentation - Rebuilding Indexes SQL Server 2005

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

defragmentationsqlindexes2005serverrebuilding

Problem

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.

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?

I read REBUILD makes things run slower. Is that just while the

indexes are being rebuilt? (or forever after)

How long will it take to rebuild all indexes / or rather each one?

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!

Solution

Yes you can rebuild while the database is online with active users. It's definitely better to do it off-peak if possible.

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.

Again, the duration of the rebuild depends on your specific setup. You should try running this on an equivalent development or staging server.

No other associated side-effects. Once the rebuild is complete you'll be back up and running as usual.

Context

StackExchange Database Administrators Q#10901, answer score: 7

Revisions (0)

No revisions yet.