(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)
There was a Twitter #sqlhelp question this week about whether to be concerned about extent scan fragmentation or logical scan fragmentation when using DBCC SHOWCONTIG.
The real answer is to stop using DBCC SHOWCONTIG as it was deprecated with the release of SQL Server 2005, and use sys.dm_db_index_physical_stats, the replacement DMV I designed, instead.
The new DMV doesn’t report extent scan fragmentation because it doesn’t have to. If you think about it, extent scan fragmentation is a measure of how many times a key-order scan of the leaf level of the index moves from an extent to another extent which isn’t the next physically-contiguous extent. Logical fragmentation is a measure of how many times a key-order scan of the leaf level of the index moves from a page to another page which isn’t the next physically-contiguous page. So extent scan fragmentation is actually part of logical fragmentation, which is why the new DMV doesn’t report it.
Back to the original question: use logical scan fragmentation if you’re still using the old DBCC command, but plan to move to the DMV.
And there’s another reason: extent scan fragmentation in DBCC SHOWCONTIG is documented as not being accurate if the index is on a filegroup with multiple files. It’s been that way since it was originally written for SQL Server 7.0 and we decided not to fix it in SQL Server 2000 or later.