Posted by wizofaus on 01/14/07 02:15

Erland Sommarskog wrote:
> (wizofaus@hotmail.com) writes:
> > I agree this sort of thing would be preferable, and more efficient, but
> > as it is, providing I don't get the bad plan problem, it's efficient
> > enough as it is, and I'm wary of making too many big changes at this
> > point. That sort of thing I'd prefer to leave for the next version.
>
> But keep in mind that the solution you have now will not scale well. If
> the data in production is ten times larger than you have expected, you
> will get ten times more execution time, even with the good plan.

Sure. I definitely plan on doing some query optimization and
consolidation for the next version. Your routines may well come in
handy, so thanks.
>
> > The thing is, I just tried exporting all the records to a CSV file,
> > creating a clean database with the same tables/indices, re-importing
> > all the records, and the same problem DOESN'T happen: I can run that
> > query with the "odd" value first, then with the common value, and both
> > queries run fast. So something else has happened to my database that
> > surely I must be able to reset somehow?
>
> I eavesdropped a discussion at PASS in Seattle last year, when a guy
> had done extensive tests, and he could repeat a scenario that depending
> on which order he loaded the same data, he would get different plans,
> good or bad. I presume that part of the answer lies what exactly is in
> the statistics. Normally, statistics are only samples, and if the
> statistics does not well reflect the data distribution, your plans
> will not always be the best.
>
Well I found another solution - reindex the table.

I ran

dbcc dbreindex('mytable', ' ', 90) on the initial database, and now the
problem is gone away. My main concern was that if we did see this
problem occuring in productoion databases, how could we fix it, other
than changing the code, and at least now I have such a solution, and
it's a bit less drastic than exporting and re-importing all the data
(which potentially could have taken hours).

I'm not sure whether 90 is the best parameter value here, that was just
from the MSDN article.

[Back to original message]