The Curious Case of… extent scan fragmentation

(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,