469,266 Members | 1,768 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

dbcc reindex issue - - I don't understand!!

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 26 '06 #1
3 7858
Stu
Did you not already post this message before, or am i experiencing deja
vu?

Your database id's are different, which means that you ran the
showcontig command on a different database. Did you make a backup and
restore it?

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 26 '06 #2
Raziq Shekha (ra**********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 26 '06 #3
Yes, I did repost this because i did not get any answers the first time.
Yes, I did restore the backup of a database and created a new database.

*** Sent via Developersdex http://www.developersdex.com ***
Jun 26 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Merrua | last post: by
11 posts views Thread by Mark Goldin | last post: by
5 posts views Thread by ryann18 | last post: by
19 posts views Thread by so many sites so little time | last post: by
8 posts views Thread by mohammaditraders | last post: by
6 posts views Thread by Bishop | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.