corrupted(?) sql server 2000 table

Hi all,
I have identified a table in our database that seems to have I/O
issues. When I run a update statistics on this table it takes about 1
minute 30 secs. I renamed the table and did a select * into a new
table. When I run update statistics on the new table, it runs in secs.
I've ran CHECKDB, CHECKDB and no errors shows up for the database or
the table itself. We therorized that when we run
queries(insert,updaet, select) on the bad table, SQL server is trying
to read the table, error out, reads the table again on the disk and
continues until it is successfuly. We have indentified that why running
against this table itself, it generates high I/O activity. We can't
seem to identifiy any errors and are currently stump as to why it's
seems to be this table only.
Our currently theory is there is a table corruption but at a level that
SQL and/or Windows 2003 OS can't detect.
Any insight would be great. Thanks!
ILoveSQL wrote:
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
>
How large are the tables bytes, rows, columns?
What indexes, if any, are on the both the tables?
If so, How often are indexes rebuilt?
Are indexes exactly the same on both tables?
In query analyzer execution plan, does one query perform paralleism
againt old table but doesn't against the new table?
Are the tables created on the same disk drive?

> Any insight would be great. Thanks!
|||It sounds like you have a very highly fragmented table. When you made the
new one you removed some of the fragmentation. Do you have a clustered index
on the original table? Did you try to reindex it?
Andrew J. Kelly SQL MVP
"ILoveSQL" <steve.trinh@.gmail.com> wrote in message
news:1160167801.596185.75220@.h48g2000cwc.googlegro ups.com...
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
> Any insight would be great. Thanks!
>
|||Hi,
Try doing a DBCC DBREINDEX on that table. If it does not help then try
unloading and reloading
the data using BCP OUT and BULK Insert.
Thanks
Hari
"ILoveSQL" <steve.trinh@.gmail.com> wrote in message
news:1160167801.596185.75220@.h48g2000cwc.googlegro ups.com...
> Hi all,
> I have identified a table in our database that seems to have I/O
> issues. When I run a update statistics on this table it takes about 1
> minute 30 secs. I renamed the table and did a select * into a new
> table. When I run update statistics on the new table, it runs in secs.
> I've ran CHECKDB, CHECKDB and no errors shows up for the database or
> the table itself. We therorized that when we run
> queries(insert,updaet, select) on the bad table, SQL server is trying
> to read the table, error out, reads the table again on the disk and
> continues until it is successfuly. We have indentified that why running
> against this table itself, it generates high I/O activity. We can't
> seem to identifiy any errors and are currently stump as to why it's
> seems to be this table only.
> Our currently theory is there is a table corruption but at a level that
> SQL and/or Windows 2003 OS can't detect.
> Any insight would be great. Thanks!
>