THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

SQL Server Quiz: Is 100% logical scan fragmentation always worse than 85% logical scan fragmentation?

Logical scan fragmentation as reported by DBCC SHOWCONTIG is bad for queries that perform large scans such as table/index scans. Often, we hear that the higher the logical scan fragmentation percentage is, the worse it is for the scans.

So the question is: is 100% logical scan fragmentation always worse than 85% (or 80% or 90%) logical scan fragmentation for table/index scans?

Published Friday, May 22, 2009 5:43 PM by Linchi Shea
Filed under: ,



TiborKaraszi said:

How about 0.01% logical scan fragmentation being disastreous?

May 23, 2009 10:05 AM

Linchi Shea said:

Okay, but let's ignore heaps for now.

May 25, 2009 2:55 PM

TiborKaraszi said:

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.

May 26, 2009 10:30 AM

Linchi Shea said:

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.

May 26, 2009 10:44 AM

TiborKaraszi said:

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. :-)

May 26, 2009 3:07 PM

Linchi Shea said:

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

June 22, 2009 12:09 PM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement