Extent scan fragmentation 1

[sendba]

Hai Experts

I am dropping and recreating the Indexes for a table which has extent scan fragmentation of 65% but doing so i am not seeeing any changes in the percentage.

Please Help

Regards
Sen

 

[NoCoolHandle]

What is your result from dbcc showcontig?

Did you try dbcc reindex or dbcc indexdefrag?

this saves you needing to manually rebuild all nonclusted indexes if you are doing it to your clusted index.

Rob

 

[sendba]

DBCC SHOWCONTIG scanning 'ActivationPin' table...
The output is

Table: Active(1716305274); index ID: 1, database TABLE level scan performed.
- Pages Scanned................................: 5
- Extents Scanned..............................: 3
- Extent Switches..............................: 2
- Avg. Pages per Extent........................: 1.7
- Scan Density [Best Count:Actual Count].......: 33.33% [1:3]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 761.2
- Avg. Page Density (full).....................: 90.60%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

[vongrunt]

5 pages?

This table is too small to worry about.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NoCoolHandle]

Ok... I see the issue.
You have Mixed extents.

Any table less that 8pages (9) i think will end up "sharing" pages with other tables/indexes.

YOur entire table is ONLY 5 pages. YOu will never get scan density below what you have untill it is large enouth.

The nice thing here is that your extent switches are 1 less than the extents which shows your pages are in order. They are just spread over 3 extents.

Rob