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