Advice on re-indexing please

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 article and do the same select again, the result is ok.

Please advice on how to solve this problem?

Thank you

SQL Server 2000, i need to check on the service pack.

My system suppose to have records coming in anytime of the day (24 hrs standby). If I do DBCC DBREINDEX, it will lock up the table, right? Any alternative for this.
Also, from the dbcc showcontig, is there any problem that you all see?

I am using SQL Server 2000 SP3. Any problem?

Not sure what caused the problem but you can try:

0. Backup db
1. Create table table tmp_article
2. Copy distinct rows from article to tmp_article
3. exec sp_rename 'article', 'article_old'
4. exec sp_rename 'tmp_article', 'article'

Before and/or after that you can try dbcc checkTable 'article'.

No, I am not using SAN.

The problem is that after insert/update of the records in that table and upon select it, there will be duplicate record. Actually, I am scheduling a DBCC DBREINDEX at 6am in the morning. Funny thing is that after doing a DBCC DBREINDEX, the select is ok. But still, this won't solve my problem and I cannot be doing a dbreindex on every update/insert? I really can't figure out what's causing this. Turning crazy soon and my user is pushing me for a solution.

Have you overlooked my post?

Hi mmarovic, I have not overlooked your post. But this is live data that we are talking about. I don't think I can do that. But anyway, all the records are distinct because it has a unique id as the primary key, but somehow when selecting after an update/insert, it shows duplicate record. Once reindex, it is ok.
The schema of the table is
id int PK
name varchar 100
ranking decimal(5,2)

Initially, this table consists of id and name only with around 1 million records. Later, I use the design table to add the ranking column into it (instead of using the alter table ...).
Not sure, whether this is the one that caused the problem.
If yes, how to resolve without any downtime?
Thanks.

During your downtime, manually drop ALL the indexes off this table, including the primary key. Make sure you have a backup before doing it. Remove the statistics. Recreate the index. Update statistics.

Do you have auto create and update of statistics on?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

How many processors are there on the server?
Is it happening due to parallelism?
Have you tried running the same query with Maxdop 1?

Sorry to say that I am not a DBA and also we don't have a DBA.

So as suggested by Derrick, "During your downtime, manually drop ALL the indexes off this table, including the primary key. Make sure you have a backup before doing it. Remove the statistics.ecreate the index. Update statistics."
Err.... is this risky? Because the table includes more than 1 million records.

"Do you have auto create and update of statistics on?"
Yes, this is on

Re: gurucb, I have only 1 CPU in my server, thus I don't think it is caused by parallelism.

By the way, is there any difference between doing redbindex and "dropping and recreating index manually"?

My solution doesn't require downtime. exec sp_rename takes a few ms. If user tries to select from that table during that very short period he would recieve an error. That way you will have old table renamed to investigate more and new table that hopefully will not have the same problem. I applied that technique when I had similar problem on mssql server 6.5.