1

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?

yagmoth555
  • 16,758
  • 4
  • 29
  • 50
  • 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. –  Mar 09 '11 at 13:07
  • 1
    shrinking files is usually a bad idea.... – Mitch Wheat Mar 09 '11 at 13:07
  • I never said I would shrink the files, just reorganize pages so free space is contigous. –  Mar 09 '11 at 13:13
  • @TiQ: two lines up: "but the actual database file under Tasks/Shrink/File " – Mitch Wheat Mar 09 '11 at 13:14
  • Making the white space contigous within the database file doesn't do much if anything for performance. – mrdenny Mar 27 '11 at 16:59

1 Answers1

1

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%.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • 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