Posted by Erland Sommarskog on 01/13/07 23:45

(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.

> 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.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]