Rebuild index issue - - strange

[Raziq Shekha]

Hi Folks,

SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
the command :

dbcc dbreindex ('tablename')
go

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%
Second output is from after the reindex:

DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
8
TABLE level scan performed.
- Pages Scanned................................: 303177
- Extents Scanned..............................: 37964
- Extent Switches..............................: 42579
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 89.00% [37898:42580]
- Logical Scan Fragmentation ..................: 43.19%
- Extent Scan Fragmentation ...................: 24.78%
- Avg. Bytes Free per Page.....................: 75.1
- Avg. Page Density (full).....................: 99.07%
Following are my concerns:

The following numbers are all higher after reindex than before reindex:

pages scanned, extent switches, logical scan fragmentation, avg bytes
free per page, avg page density.

scan density is lower after reindex than before reindex

Seems to me that the numbers that are higher after reindex should be
lower and numbers that are lower after reindex should be higher? I
didn't specify the fill factor in the dbcc reindex command so it should
have used the default fill factor. The fill factor has never been
changed on this machine.

Am I missing something?

Thanks,
Raziq.

*** Sent via Developersdex http://www.developersdex.com ***

[GlennThomas5]

if you look at your database ID's, they are different. did you run
this on 2 different databases?

Raziq Shekha wrote:

Hi Folks,

SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran
the command :

dbcc dbreindex ('tablename')
go

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%
Second output is from after the reindex:

DBCC SHOWCONTIG scanning 'PlannedTransferArchive' table...
Table: 'PlannedTransferArchive' (1975014117); index ID: 1, database ID:
8
TABLE level scan performed.
- Pages Scanned................................: 303177
- Extents Scanned..............................: 37964
- Extent Switches..............................: 42579
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 89.00% [37898:42580]
- Logical Scan Fragmentation ..................: 43.19%
- Extent Scan Fragmentation ...................: 24.78%
- Avg. Bytes Free per Page.....................: 75.1
- Avg. Page Density (full).....................: 99.07%
Following are my concerns:

The following numbers are all higher after reindex than before reindex:

pages scanned, extent switches, logical scan fragmentation, avg bytes
free per page, avg page density.

scan density is lower after reindex than before reindex

Seems to me that the numbers that are higher after reindex should be
lower and numbers that are lower after reindex should be higher? I
didn't specify the fill factor in the dbcc reindex command so it should
have used the default fill factor. The fill factor has never been
changed on this machine.

Am I missing something?

Thanks,
Raziq.

*** Sent via Developersdex http://www.developersdex.com ***