SQL Server index fragmentation: seek vs scans

There is a common missunderstanding about index fragmentation. You probably heard that fragmentation is the root cause of many performance issues, and yes it is in some cases, but not all cases. You may have 99% fragmentation and the queries are still very efficient, and in other cases you may experience performance issues when fragmentation is more that 15%. 

Why do some queries run slow and some not when the indexes are fragmented?

Well it depends on what kind of query plan you got, and as you probably already guest from the title of this blog, it has to do with seeks vs scans. If you get a scan (or read ahead) against a fragmented table, then you suffer from fragmentation, but if you get index seeks you are not affected at all.

Sometimes when you experience performance issues and you rebuild the index to solve the problem, it doesn’t have to do anything with fragmentation. What? It solved the problem! Yes it did, but there are some other thing that happens at the same time as the index rebuild:

• The statistics against the index are updated
• The queries against the table are recompiled

These two factors may have much more impact on the performance than index rebuild do.

If you have performance issues for some queries that used to run fast:

• take a look at the query plan and see if you got index scans or big difference between estimated and actual number of records.
• updated the statistics for the table

/Håkan Winther
Twitter: @h_winther