468,103 Members | 1,172 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,103 developers. It's quick & easy.

Rebuild index issue - - strange

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 ***
Jun 16 '06 #1
1 2206
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 ***


Jun 19 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

29 posts views Thread by shmartonak | last post: by
10 posts views Thread by wackyphill | last post: by
1 post views Thread by Solo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.