Interesting. It seems a like the query plan alternatives are perhaps a bit fractal and sometimes you just end up with a really bad one.

I had an interesting experience as a student (in the early 90s), writing a calibration algorithm using Newton's method that diverged and oscillated wildly when starting out in some regions - I had to use a fairly good initial guess to stop that occurring.

My supervisor wouldn't believe me, and it wasn't until a few years later that I read James Gleick's 'Chaos' that I realised it was a classic example of chaotic instability. Made me realise it can crop up under the most innocuous conditions.

Even six years later on, I still think that while the automatons do a great job most of the time, and even after all the 'it's context dependent' counter arguments, it would be great for Transact SQL to offer specific atomic predictable control over elements of query plan generation.

Sure, used badly, it might cause great woe, but I'm pretty sure there are contexts that actually won't vary much over the database's life where it would work to great advantage.