why index could be damaged?

[Alex]

we have query

SELECT SUM(T.AmountInput) as AmountTotal
FROM otrnTransaction T
WHERE
IsAutomaticPosting =0
AND (IsCashierAudited = 1 AND IsNightAuditorAudited = 0)

it was ruuning well until yesterday .
Today it just imposible to get result from it.

I run CHECKDB
DBCC results for 'otrnTransaction'.
There are 20067 rows in 384 pages for object 'otrnTransaction'.
...
CHECKDB found 0 allocation errors and 0 consistency errors in database 'BdHydro'

after running DBCC DBREINDEX (otrnTransaction)
the query start working well

What could cause damage to index ?
Thank you

[Ananth]

Do you have "Auto Update Statistics" turned on for the database? If you don't, chances are that the statistics information degraded to an extent which made the query processor to decide on not using the index....when you rebuild indexes, the statistics also get updated.



------------
Alex at 5/31/01 12:27:48 PM

we have query

SELECT SUM(T.AmountInput) as AmountTotal
FROM otrnTransaction T
WHERE
IsAutomaticPosting =0
AND (IsCashierAudited = 1 AND IsNightAuditorAudited = 0)

it was ruuning well until yesterday .
Today it just imposible to get result from it.

I run CHECKDB
DBCC results for 'otrnTransaction'.
There are 20067 rows in 384 pages for object 'otrnTransaction'.
...
CHECKDB found 0 allocation errors and 0 consistency errors in database 'BdHydro'

after running DBCC DBREINDEX (otrnTransaction)
the query start working well

What could cause damage to index ?
Thank you

[Alex]

I verified
Auto create statistics is on
Auto update statistics is on

Server was upgraded from Sql server pack 1
to Sql Server pack 3

and problem with the query started after upgrade

Thank


------------
Ananth at 5/31/01 1:40:52 PM

Do you have "Auto Update Statistics" turned on for the database? If you don't, chances are that the statistics information degraded to an extent which made the query processor to decide on not using the index....when you rebuild indexes, the statistics also get updated.



------------
Alex at 5/31/01 12:27:48 PM

we have query

SELECT SUM(T.AmountInput) as AmountTotal
FROM otrnTransaction T
WHERE
IsAutomaticPosting =0
AND (IsCashierAudited = 1 AND IsNightAuditorAudited = 0)

it was ruuning well until yesterday .
Today it just imposible to get result from it.

I run CHECKDB
DBCC results for 'otrnTransaction'.
There are 20067 rows in 384 pages for object 'otrnTransaction'.
...
CHECKDB found 0 allocation errors and 0 consistency errors in database 'BdHydro'

after running DBCC DBREINDEX (otrnTransaction)
the query start working well

What could cause damage to index ?
Thank you

[saravanan]

Table and Index get fragmented due to the heavy insert, update, delete and select. By running the DBCC DBREINDEX you have defragmented the table and the Index.

A fragmentation on a table can be found by using DBCC SHOWCONTIG(objectid) if the scan density is below 85% it means the table is defragmented. It is good to run DBCC DBREINDEX once a week during the OFF PEAK HOURS. If a table doesnt have a clustered Index on it, defragment is done by creating a clustered index or by doing coping a data to a text file means of bcp and drop recreating the table.




------------
Ananth at 5/31/01 1:40:52 PM

Do you have "Auto Update Statistics" turned on for the database? If you don't, chances are that the statistics information degraded to an extent which made the query processor to decide on not using the index....when you rebuild indexes, the statistics also get updated.



------------
Alex at 5/31/01 12:27:48 PM

we have query

SELECT SUM(T.AmountInput) as AmountTotal
FROM otrnTransaction T
WHERE
IsAutomaticPosting =0
AND (IsCashierAudited = 1 AND IsNightAuditorAudited = 0)

it was ruuning well until yesterday .
Today it just imposible to get result from it.

I run CHECKDB
DBCC results for 'otrnTransaction'.
There are 20067 rows in 384 pages for object 'otrnTransaction'.
...
CHECKDB found 0 allocation errors and 0 consistency errors in database 'BdHydro'

after running DBCC DBREINDEX (otrnTransaction)
the query start working well

What could cause damage to index ?
Thank you