Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
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).

share|improve this question
What version of sql server you are using ? What is the row count of the table that you are checking the fragmentation level ? – Kin Apr 16 '13 at 16:41
extent fragmentation is Called as "External" fragmentation and it Occurs when the extents in an index are not contiguous.. Affects readahead but not as much. Depending on the row count and number of pages, it might affect performance. If the table is having less than 1000 pages, then even if you rebuild the index, it wont help much – Kin Apr 16 '13 at 16:52
@Kin ...depending on the row count and number of pages... and types of queries being run... even if you're doing full table scans it is unlikely that it will affect performance much, but for more typical query patterns it should be negligible at best. At least in my experience. – Aaron Bertrand Apr 16 '13 at 17:01
Going from your "It runs ~30 seconds on first execution, then 200 ms on second", I'd say the 2nd execution is fast because the data is cached. Now, if you want to focus better on fixing that query, I'd say you need to show us the table schema (including indexes), the query itself and the query's execution plan (the real one, not the estimated). You can also use SQL Sentry's Plan Explorer to generate the real plan and upload it here. – Marian Apr 16 '13 at 17:13
@Kin SqlServer2012. 34,530,707 rows, 8,153,456 KB data. – David B Apr 16 '13 at 17:29
up vote 6 down vote accepted

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.

share|improve this answer
Uh, oh, you may want to emphasize that on very large tables (my case 50GB+) this may be an issue. I'v experienced even if data is in memory, poor page density (even if low fragmentation) can cause issues and low performance queries and user complaints. – Janis Veinbergs Jul 19 at 6:50
@Janis My first first paragraph says "unlikely" not "impossible" and didn't I address your exact point at the beginning of my second paragraph? More importantly, how have you determined that poor page density is the exact and only cause for your user complaints? – Aaron Bertrand Jul 20 at 10:45
no worries, I just wanted to put more emphasize on that word :) Well, can't say that its the only cause. Just that there are cases where Logical Fragmentation is low and acceptable, but page density could be improved and users complain and when that Clustered Index is rebuilt, everything returns to normal. Maybe something else causes this that gets fixed by rebuild... – Janis Veinbergs yesterday

ConstantScan->NestedLoop->IndexSeek->NestedLoop->KeyLookup plan

This plan didn't access the whole table since it returned only 7,000 rows out of 34.5M rows.

The total amount of data coming off disk is miniscule compared to the size of the entire table1; random seek time to fulfill the key lookup operations seems to dominate. Fixing fragmentation issues only apply when scan operations are involved. Once the access pattern is random like it appears to be here, fragmentation -- and fragmentation metrics -- are irrelevant.

You should be able to verify what's going on by watching the disk activity in Performance Monitor or Resource Monitor while the query is running -- I expect you'll see very low disk throughput.

Assuming my analysis is correct, here are a couple of suggestions (which can be combined) to improve the query execution time, particularly with a cold cache:

  • Put the data file(s) on a storage subsystem that can better handle random reads. As a rough estimate, 30s / 7,000 rows is ~4ms average seek time, which is not bad, so this could be an expensive proposition.

  • Modify the nonclustered index to cover the query by using INCLUDE columns, thereby eliminating the need for key lookups, and hence most of the random disk activity. This is likely the best solution, even if you sacrifice a bit of extra storage space for it. Hopefully the table isn't super wide.

1 Assuming approximately equal row sizes.

Also, as an aside, DBCC SHOWCONTIG has been deprecated for quite a while -- the replacement going forward is sys.dm_db_index_physical_stats.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.