asked
index fragmentation and statistics
If an index is fragmented, how does the optimizer respond on this? In other words: how does the optimizer decide not to use an index because of its fagmentation? If statistics are outdated, is this also a reason not to use the index, or is the optimizer in that case just using outdated data? Let's say you don't rebuild your indexes but only update your statistics. Is there a point when SQL decides not to use the indexes (because of a high fragmentation) regardless of the actual statistics?
According to this msdn blog ([
http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/04/23/conor-vs-index-fragmentation-in-plan-selection.aspx][1]), fragmentation is not directly accounted for by the optimizer. If fragmentation due to page splits or fill factor cause an index to become inflated, the optimizer would respond to the higher number of pages rather than the fragmentation percentage. Statistics would still lead it to use the index when appropriate. I would personally imagine that a very tightly scoped seek would continue to almost always use the index but an index scan could reach the point where it might scan the table rather than a non-clustered index. [1]:
http://blogs.msdn.com/b/conor_cunningham_msft/archive/2010/04/23/conor-vs-index-fragmentation-in-plan-selection.aspx
15 People are following this question.
