One of the client DBAs had asked this question some time back so we thought we would post the discussion on the blog since it is likely that other DBAs/Database Developers might have this question as well. What he had noticed was this:
He ran DBCC SHOWCONTIG and saw the numbers in Scan Denisty, Logical Scan Fragmentation and Extent Scan Fragmentation (along with Extents Scanned and Extent Switches). He ran the rebuild to rebuild the indexes and after that noticed that the scan density for some of those indexes was now close to 100% and the logical scan fragmentation was also very less (1-3%) however, the extent fragmentation for many of those indexes was still over 30%.
Two things to know about this output -
1) If your indexes are small, then these values do not matter at all. Why? Because SQL Server has two types of extents: Uniform Extents and Mixed Extents. Uniform extents are extents that are owned by a single object where as mixed extents are used for more than one smaller objects. In the case of a uniform extent, all the 8 pages for the extent (an extent = 8 pages = 64KB) are allocated to one single object. That is why in smaller objects, you will see that the scan density will be low, extent scan fragmentation will be high since the object will be using say a single page in N extents. If the objects are large, then SQL Server will be using the uniform extents and you will see scan density closer to 100% if there is no fragmentation.
2) Another thing to note is that the extent scan fragmentation data that is shown in the output of DBCC SHOWCONTIG does not represent the true value if there are multiple files in the filegroup and the index belongs to that filegroup. This is actually also documented in BOL.