Advice on re-indexing please

Question

Advice on re-indexing please

Chiang

SSC Veteran

Points: 261
More actions
July 4, 2005 at 11:18 pm

#60850

The following shows the result for running the showcontig on my MS SQL 2000 database

dbcc showcontig(Article) WITH ALL_INDEXES

DBCC SHOWCONTIG scanning 'Article' table...
Table: 'Article' (734625660); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 52113
- Extents Scanned..............................: 6530
- Extent Switches..............................: 6531
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.74% [6515:6532]
- Logical Scan Fragmentation ..................: 0.20%
- Extent Scan Fragmentation ...................: 0.60%
- Avg. Bytes Free per Page.....................: 69.1
- Avg. Page Density (full).....................: 99.15%
DBCC SHOWCONTIG scanning 'Article' table...
Table: 'Article' (734625660); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned................................: 7421
- Extents Scanned..............................: 930
- Extent Switches..............................: 929
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.78% [928:930]
- Logical Scan Fragmentation ..................: 12.51%
- Extent Scan Fragmentation ...................: 2.90%
- Avg. Bytes Free per Page.....................: 2.8
- Avg. Page Density (full).....................: 99.97%
DBCC SHOWCONTIG scanning 'Article' table...
Table: 'Article' (734625660); index ID: 3, database ID: 7
LEAF level scan performed.
- Pages Scanned................................: 7474
- Extents Scanned..............................: 939
- Extent Switches..............................: 1030
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 90.69% [935:1031]
- Logical Scan Fragmentation ..................: 0.71%
- Extent Scan Fragmentation ...................: 0.32%
- Avg. Bytes Free per Page.....................: 60.2
- Avg. Page Density (full).....................: 99.26%
DBCC SHOWCONTIG scanning 'Article' table...
Table: 'Article' (734625660); index ID: 4, database ID: 7
LEAF level scan performed.
- Pages Scanned................................: 7437
- Extents Scanned..............................: 936
- Extent Switches..............................: 960
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 96.77% [930:961]
- Logical Scan Fragmentation ..................: 0.22%
- Extent Scan Fragmentation ...................: 0.53%
- Avg. Bytes Free per Page.....................: 20.2
- Avg. Page Density (full).....................: 99.75%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Every time I do a select on this table, it shows duplicate record. But when I execute the DBCC DBREINDEX on this table and do the same select again. The result is ok.

Please advice on how to solve this problem?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

Every time I do a select on this table, it shows duplicate record. But when I execute the DBCC DBREINDEX on this table and do the same select again. The result is ok.

Sounds strange, possibly a bug. I encountered and had a bug fixed where a clustered index containing a bit column caused duplicate rows to appear, but I do not know if reindexing would remove those duplicates. After you reindex, does the duplicate appear again later and you need to reindex again?

Can you produce a small repro?

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Chiang SSC Veteran Points: 261 More actions · Answer 2

After reindex, there won't be any duplicate record using the same select. However, after updating and inserting a few records and do the same select again, it shows duplicate record again and I have do a reindexing again.

Eoin The Forge SSCrazy Points: 2932 More actions · Answer 3

I'd say I had the same probelm. The index started to get corrupt with (Duplicates) - Rebuilt it and is worked fine, but a few days later it corrupted again, we had to remove it from the DB. I added the same index to a similar db in a seperate country and low and belold same thing happened. It was a non clustered non unique covering index.

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 4

Same advice again: Can you produce a repro? If so, then open a case with MS Support and record it as a bug.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/