Is high Extent Fragmentation a problem?

DBCC SHOWCONTIG scanning 'MyTable' table...
Table: 'MyTable' (2048062382); index ID: 1, database ID: 28
TABLE level scan performed.
- Pages Scanned................................: 1019182
- Extents Scanned..............................: 127400
- Extent Switches..............................: 127399
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [127398:127400]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 77.25%
- Avg. Bytes Free per Page.....................: 135.7
- Avg. Page Density (full).....................: 98.32%

I have read that Scan Density = 100% is very good, and Logical Scan Fragementation <1% is also great. 77% Extent Scan Fragmentation troubles me, but the internet says to ignore it.

I'm analyzing a single-table slow performing query. It runs ~30 seconds on first execution, then 200 ms on second and subsequent executions. I can reset this behavior with DBCC DROPCLEANBUFFERS.

Is the high Extent Scan Fragmentation an important clue?

(If not, I'll likely add another question about my single-table query).

In my experience, even if you're doing full table scans, it is unlikely that extent fragmentation will affect performance much, and for more typical query patterns it should be negligible at best. That is for queries that use cached data that fits into memory - obviously fragmentation of any kind becomes rather moot if the data is in memory and isn't being read directly off disk.

Now, you've got a table that is > 8 GB, so it is possible that extent fragmentation might be harmful to your queries. If this query is using a table scan across 34 million rows, and the worst you're getting (on first execution only!) is 30 seconds, then it is extremely unlikely that lowering that extent fragmentation number is going to help much. That 30 seconds is spent loading the data into memory, and I can't fathom that improving the extent fragmentation will buy you much there. If you have the memory to spare to keep this table in memory, perhaps you should consider a startup job or some background process that periodically runs the query without forcing a user to wait for it, ensuring that it stays fresh in the cache.

Hekaton might be for you.

StackExchange Database Administrators Q#40141, answer score: 10