Question about Extent Scan Fragmentation

I used DBCC SHOWCONTIG (UserData) to check on a script that I have that
either defrags or rebuilds indexes. I wanted to see how well it worked.
Here is the data I recieved.
DBCC SHOWCONTIG scanning 'UserData' table...
Table: 'UserData' (501576825); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned........................: 1146
- Extents Scanned.......................: 148
- Extent Switches.......................: 150
- Avg. Pages per Extent..................: 7.7
- Scan Density [Best Count:Actual Count]......: 95.36% [144:151]
- Logical Scan Fragmentation ..............: 0.35%
- Extent Scan Fragmentation ...............: 87.84%
- Avg. Bytes Free per Page................: 560.8
- Avg. Page Density (full)................: 93.07%
I was worried about the Extent Scan Fragmentation so I ran.
DBCC DBREINDEX (UserData)
Then the numbers showed:
DBCC SHOWCONTIG scanning 'UserData' table...
Table: 'UserData' (501576825); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned........................: 1140
- Extents Scanned.......................: 144
- Extent Switches.......................: 143
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 77.78%
- Avg. Bytes Free per Page................: 521.1
- Avg. Page Density (full)................: 93.56%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
The extent scan fragementation was still bad, so I tired once more wth:
DBCC DBREINDEX (UserData)
After this try here are the numbers:
DBCC SHOWCONTIG scanning 'UserData' table...
Table: 'UserData' (501576825); index ID: 1, database ID: 9
TABLE level scan performed.
- Pages Scanned........................: 1140
- Extents Scanned.......................: 144
- Extent Switches.......................: 143
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 1.39%
- Avg. Bytes Free per Page................: 521.1
- Avg. Page Density (full)................: 93.56%
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Much better this time.
Is this common that it takes multiple tries or am I not understanding
something correctly. This is a bit of new information for me, but I was under
the impression that DBCC DBREINDEX would rebuild my index.
ThanksAs documented in BOL, the extent scan fragmentation calculation does not
work for indexes over multiple files. The first time you rebuilt, I'm
guessing it found space on several files to use and the second time only on
one file.
Logical Scan Fragmentation is what you want to pay attention to anyway - see
the whitepaper below for more details.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Cooper" <Cooper@.discussions.microsoft.com> wrote in message
news:F7DB6928-1B3E-4B39-88C2-BEB868E4FC71@.microsoft.com...
> I used DBCC SHOWCONTIG (UserData) to check on a script that I have that
> either defrags or rebuilds indexes. I wanted to see how well it worked.
> Here is the data I recieved.
> DBCC SHOWCONTIG scanning 'UserData' table...
> Table: 'UserData' (501576825); index ID: 1, database ID: 9
> TABLE level scan performed.
> - Pages Scanned........................: 1146
> - Extents Scanned.......................: 148
> - Extent Switches.......................: 150
> - Avg. Pages per Extent..................: 7.7
> - Scan Density [Best Count:Actual Count]......: 95.36% [144:151]
> - Logical Scan Fragmentation ..............: 0.35%
> - Extent Scan Fragmentation ...............: 87.84%
> - Avg. Bytes Free per Page................: 560.8
> - Avg. Page Density (full)................: 93.07%
> I was worried about the Extent Scan Fragmentation so I ran.
> DBCC DBREINDEX (UserData)
> Then the numbers showed:
> DBCC SHOWCONTIG scanning 'UserData' table...
> Table: 'UserData' (501576825); index ID: 1, database ID: 9
> TABLE level scan performed.
> - Pages Scanned........................: 1140
> - Extents Scanned.......................: 144
> - Extent Switches.......................: 143
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 77.78%
> - Avg. Bytes Free per Page................: 521.1
> - Avg. Page Density (full)................: 93.56%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> The extent scan fragementation was still bad, so I tired once more wth:
> DBCC DBREINDEX (UserData)
> After this try here are the numbers:
> DBCC SHOWCONTIG scanning 'UserData' table...
> Table: 'UserData' (501576825); index ID: 1, database ID: 9
> TABLE level scan performed.
> - Pages Scanned........................: 1140
> - Extents Scanned.......................: 144
> - Extent Switches.......................: 143
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.31% [143:144]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 1.39%
> - Avg. Bytes Free per Page................: 521.1
> - Avg. Page Density (full)................: 93.56%
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Much better this time.
> Is this common that it takes multiple tries or am I not understanding
> something correctly. This is a bit of new information for me, but I was
under
> the impression that DBCC DBREINDEX would rebuild my index.
> Thanks