Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Say you have a database with heavy index fragmentation. Say this database also has a lot of free space due to frequent deletes in its data file. This free space is not contiguous.

If I rebuild all indexes to remove fragmentation and then reorganize the database pages so allocated pages and free pages are contiguous, would this cause further fragmentation in my indexes?

I guess the question can be posed as: if it matters, which should I do first, reorganize or rebuild?

share|improve this question

migrated from stackoverflow.com Mar 9 '11 at 13:20

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

    
Just Rebuild..... –  Mitch Wheat Mar 9 '11 at 13:07
    
When I say reorganize, I do not mean reorganizing indexes, but the actual database file under Tasks/Shrink/File for instance, or using DBCC command. –  TiQ Mar 9 '11 at 13:07
1  
shrinking files is usually a bad idea.... –  Mitch Wheat Mar 9 '11 at 13:07
    
I never said I would shrink the files, just reorganize pages so free space is contigous. –  TiQ Mar 9 '11 at 13:13
    
@TiQ: two lines up: "but the actual database file under Tasks/Shrink/File " –  Mitch Wheat Mar 9 '11 at 13:14
show 1 more comment

1 Answer

You should do one or the other, but not both. The outcome of both operations is the same thing. Which one you should do will depend on how fragmented your index is, and if you want the stats updated at the same time (rebuild updates the stats for the index).

It will also depend on what edition you have. Rebuilding indexes is an offline operations unless you have the Enterprise edition or higher of SQL Server 2005 or newer.

Typically people will start by defrag their indexes when the fragmentation shows as less than 70% and rebuild when it is higher than 70%.

share|improve this answer
    
The last paragraph is totally out of whack. Leave alone when < 10%, defrag when < 30%, rebuild when over 30% is what I've seen. –  Jon Seigel Mar 27 '11 at 15:51
    
Every system, and every index should probably have different rebuild vs. defrag numbers as the data in each index has different data, and each index will perform differently under load. –  mrdenny Mar 27 '11 at 16:57
    
Agreed, but why defrag when it's higher than 70%? I don't get that. Did you transpose rebuild and defrag in that sentence? –  Jon Seigel Mar 27 '11 at 17:01
    
Yeah, I think I did get them backwards (granted it's pretty early at the moment, at least for me). When fragmentation is low defrag is usually faster, when fragmentation is higher rebuild is usually faster. I'll update my answer. –  mrdenny Mar 27 '11 at 17:04
add comment

Your Answer

 
discard

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