-
why index could be damaged?
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
-
why index could be damaged? (reply)
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
-
why index could be damaged? (reply)
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
-
why index could be damaged? (reply)
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|