How about 0.01% logical scan fragmentation being disastreous?

Okay, but let's ignore heaps for now.

Hehe, I figured you would consider heaps cheating.

I've been trying to "see" what you are thinking here, but I think I fail, I'm afraid. I have two possible theories, but none of them feel right...:

a) Few pages. Say you have only, for instance, 6 pages. It doesn't matter here is all are totally non-sequantial or happens to be in order and perhaps even on same extent. I.e., irrelevant for small b-trees. Just consider the case when all pages needed happens to be hot enough to already be in cache.

b) Weakness in how logical scan fragmentation is calculated.

To answer this with certainity, I guess one would need access to source code for either sys.dm_db_index_physical_stats or DBCC SHOWCONTIG (depending on which of these you refer to).

Say that for one case with 100% lsf, SQL Server have the pages pretty close to each other and perhaps even jump "back and forth" while scanning. Say a scan would be performed as, following linked list from first logical page and forward (Extent#, Page#):

E1P1, E100P1, E1P2, E100P2, E1P3, E100P3

For above I think we would end up with extent 1 and 100 in cache pretty early on during the scan operation (cache at some level, either SQL Server level or SAN level).

Compare above to below, say with lsf 80%

E1P1, E2034P3, E843P6, E843P7, E96P2, E555P6

I.e., even though a few happens to be "adjacent" (causing lsf be lower than above), the majority are on new extents which are pretty far from each other.

It's b). a) is too trivial. The more I look at logical scan fragmentaiton, the more I become convinced that it's rather unreliable to draw any query performance conclusion from its values. In other words, it's a myth that higher logical scan fragmentation values may have more severe query performance impact.

Interestng that you sould say that. I've also had this creeping feeling that this should be looked into some more. OTOH, if logical scan fragmentation is the "only thing" we have available then perhaps we should use it as a "good enough" tool? Hmm, I wonder whether other columns in sys.dm_db_index_physical_stats can be of help here... But... wife time here. :-)

In this previous post I asked whether 100% logical scan fragmentation is always worse than 85% local