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.

Microsoft recommends not bothering to rebuild indexes that have less than 1,000 pages (from memory I think it's because they are so small that they will be held in RAM).

If I'm not rebuilding them, then they'll become heavily fragmented. In that case is there any point in having them at all? I'm imagining a phone book where every single entry is in the wrong place - it would be useless!

For clarification I'm mainly talking about non-clustered indexes, though I'd be interested to know whether or not the answer would be different for clustered indexes.

The answer to this question suggests that indexes can help to avoid blocking issues behind row locks, which would be a reason why the indexes may still be useful if it is correct and applies to SQL Server.

share|improve this question

1 Answer 1

up vote 15 down vote accepted

Just because the index doesn't require rebuilding does no mean is useless. A 99999% fragmented index is still 9999 times better than a heap for looking up a key. The negative effects of fragmentation are heavily, heavily, heavily, heavily overestimated in popular DBA culture.

Locking is a valid point (w/o indexes all read-write operations are guaranteed to collide), but not the only one. Even if the data is read-only, so locking is never an issue, it still needs an index for seek and range scans operations.

I'm imagining a phone book where every single entry is in the wrong place

This is a very misguided interpretation of what fragmentation is and its effects. A fragmented phone book still has all the entries in order. Looking up an entry still takes 2-3 page flips, it will be something like:

  • the cover says to find 'P's go to page 645.
  • page 645 says to find 'PA's go to page 56.
  • page 56 says all 'PAUL's are on page 646.

So yes, you flip back and forth 2-3 times. The fragmented phonebook also may have every page half-blank, so it weight twice as much as the newly minted phonebook.

Compare it with the heap:

  • the cover will say I don't know where 'PAUL's are, but if they're in the book, I'm sure they're between the front cover and the back cover! And do remember that finding the first 'PAUL' does not mean you found all of them...

The only data/workload that really doesn't need indexes is columnstore/data warehousing. Columnstore access and processing is completely different and can get away w/o indexes, because every query is expected to scan all data anyway. Segment elimination, per-column IO and other forces contribute to keeping these always-end-to-end scans competitive.

share|improve this answer
    
lol, I didn't even look what the linked question was... –  Remus Rusanu Nov 27 '13 at 18:49
    
Nice! I was thinking of asking how costly it is to delete data.. I think this pretty much answered it as well.. –  Question Overflow Nov 28 '13 at 3:42
    
I'm glad that I asked the question now as my understanding of index fragmentation was rather incorrect! –  paulH Nov 28 '13 at 15:24
    
@RemusRusanu your link indicates that you recommend shrinking data files to reduce index fragmentation, when it seems that all other SQL experts that I'm familiar with are very passionate about how they do not recommend shrinking data files, and how shrinking can cause index fragmentation. (See brentozar.com/archive/2009/08/… and sqlskills.com/blogs/paul/…). Care to elaborate? –  dev_etter Dec 3 '13 at 21:03
    
@dev_etter: is called Poe's law –  Remus Rusanu Dec 3 '13 at 21:36

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.