Why “Extent Scan fragmentation” remains high after rebuilding index

According to BOL, extent scan fragmentation is; “Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.”

It is also referred to as “physical fragmentation”, which basically means that the extents on the disk are not contagious.
If rebuilding an index did not bring this value down, it means your physical volume is fragmented.

In my case, I have a sql server running on a netapp san (FAS 3140)

I have a table called objects containing 12+ million records and a clustered index and a couple of non clustered indexes.

When running the following command, I get extent scan fragmentation of 60.59%
dbcc showcontig (Objects,IX_ObjectsExpires)

DBCC SHOWCONTIG scanning 'Objects' table...
Table: 'Objects' (309576141); index ID: 12, database ID: 17
LEAF level scan performed.
- Pages Scanned................................: 30593
- Extents Scanned..............................: 3852
- Extent Switches..............................: 3917
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 97.63% [3825:3918]
- Logical Scan Fragmentation ..................: 0.88%
- Extent Scan Fragmentation ...................: 60.59%
- Avg. Bytes Free per Page.....................: 683.2
- Avg. Page Density (full).....................: 91.56%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Alter rebuilding the index

Alter index IX_ObjectsExpires ON [Objects] Rebuild;
The value of extent scan fragmentation is gone up!

DBCC SHOWCONTIG scanning 'Objects' table...
Table: 'Objects' (309576141); index ID: 12, database ID: 17
LEAF level scan performed.
- Pages Scanned................................: 31109
- Extents Scanned..............................: 3889
- Extent Switches..............................: 3888
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [3889:3889]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 69.17%
- Avg. Bytes Free per Page.....................: 806.1
- Avg. Page Density (full).....................: 90.04%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

-- as can be seen, the logical fragmentation has dropped to 0, but the extent fragmentation has gone up!!

I run “reallocate” command on the volume containing the database. please note that this is a Netap specific command that reorders the block on a volume. Your SAN vendor will provide a different command.

reallocate start -f /vol/ascio_otherdb_data_vol

During reallocate, cpu gone up from 1 to around 27 % on the filer.
But no performance hit was noticed on the database. After the reallocation was completed, I rebuild the index again;

alter index IX_ObjectsExpires ON [Objects] Rebuild;

and hooray ..extent fragmentation set back to almost 0.15%

DBCC SHOWCONTIG scanning 'Objects' table...
Table: 'Objects' (309576141); index ID: 12, database ID: 17
LEAF level scan performed.
- Pages Scanned................................: 31109
- Extents Scanned..............................: 3889
- Extent Switches..............................: 3888
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 100.00% [3889:3889]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.15%
- Avg. Bytes Free per Page.....................: 806.0
- Avg. Page Density (full).....................: 90.04%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


-- for a volume size of 400 GB, it took around 1 hour t complete the reallocation command on the filer.

Mon Mar 7 17:28:52 GMT [LDN-HA-SAN01: wafl.scan.start:info]: Starting file reallocating on volume ascio_otherdb_data_vol.
Mon Mar 7 18:26:41 GMT [LDN-HA-SAN01: wafl.reallocate.full.done:info]: Full reallocation on '/vol/ascio_otherdb_data_vol' is complete.