Subject Re: How likely is corruption of indexes in this system ?
Author Matt Clark
--- In, "Adam" <s3057043@...> wrote:
> --- In, "Matt Clark" <bzugda@> wrote:
> But it also makes no sense to me as a cause of the observed anomaly.
> It the select and update are using the same criteria, then they would
> (almost certainly) use the same plan to locate the record. If the
> index was indeed missing data, then surely it would miss on both the
> update and select.
> Are you running both statements inside the same snapshot transaction?
> (Transaction isolation may be hiding certain records from one or the
> other). Do you have any update triggers that might (directly or
> indirectly) deleting or inserting records thus effecting the counts?

Once we'd identified there was an problem I did a simple select and
update test in an isql window with the row count and plan on and I got
a difference number of rows affected with the same criteria. I too
would expect to see the same plan used since the criteria were the
same but I'm pretty sure we saw a different plan. The row count was
definitely different.
Without an alternative explanation and since the daily rebuild
prevents the problem I still have to assume corruption is the cause
and continue with the rebuilds.