Posted by Erland Sommarskog on 06/26/06 22:06

Raziq Shekha (raziq_shekha@anadarko.com) writes:
> for all tables in the database. Then I compared the dbcc showcontig
> with all_index output from before and after the reindex and on the
> largest table in the database I found this. First output is prior to
> reindex:
>
>
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 7
> TABLE level scan performed.
> - Pages Scanned................................: 184867
> - Extents Scanned..............................: 23203
> - Extent Switches..............................: 23324
> - Avg. Pages per Extent........................: 8.0
> - Scan Density [Best Count:Actual Count].......: 99.07% [23109:23325]
> - Logical Scan Fragmentation ..................: 11.13%
> - Extent Scan Fragmentation ...................: 35.46%
> - Avg. Bytes Free per Page.....................: 60.0
> - Avg. Page Density (full).....................: 99.26%

With this scan density, defragmentation may be no be very useful.

> DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
> Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
> 8
> TABLE level scan performed.
> - Pages Scanned................................: 303177

I've also seen this that the reserved space for the table increases
and almost double. My speculation have been that space is reserved
for future reindex operations, but I have not dug into it.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]