I'll try and keep this short and simple.  Cut to the chase the best I can.  Could easily dive into this further and may do so if there is some interest generated.  

When implementing an index maintenance strategy there are cases where an ALTER INDEX REORGANIZE is used when logical fragmentation is between say 5% and 30%; if the logical fragmentation is greater than 30% then the ALTER INDEX REBUILD command is used.  This can leave a lot of physical fragmentation.  The REORGANIZE places the pages back in order, but does not clear out the extent fragmentation.   What happens with this maintenance strategy is that some indexes may never exceed the logical fragmentation to be rebuilt.  Leaving plenty of pesky extent switches.   Over time you may have some heavily fragmented indexes and not even know it.
For reference:
  • DBCC SHOWCONTIG was replaced by the sys.dm_db_index_physical_stats dynamic function.
  • DBCC SHOWCONTIG does not work well with partitions
  • DBCC SHOWCONTIG returns a value for scan density.  A value showing physical/extent fragmentation
  • sys.dm_db_index_physical_stats  does not return a field for extent fragmentation.  Except in the case of a heap.
  • "The fragmentation level of an index or heap is shown in the avg_fragmentation_in_percent column. For heaps, the value represents the extent fragmentation of the heap. For indexes, the value represents the logical fragmentation of the index."
  • sys.dm_db_index_physical_stats column/field avg_fragmentation_in_percent returns logical fragmentation.
  • "Logical Fragmentation  -This is the percentage of out-of-order pages in the leaf pages of an index. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page. "

I may need to draw a picture for this....Pardon my ASCII representation.  Below is an example of where each leaf node page points to the next page and are physically in order.


If LeafPage2 is after LeafPage6, LeafPage1 still points to LeafPage2.  The pointers stay the same, logical fragmentation is about the order of the pages when they are stored.

The potential issue with this, is that the logical fragmentation can be perfect and the pages will be stored in different extents.  This is extent switching.  Logical fragmentation is often a good indicator of extent switching, but not necessarily.

If there is any interest I'll go ahead and create  a test scenario and demonstrate how this happens.