Today's editorial was originally published on Sept 13, 2010. It is being re-run as Steve is away at training.

I was re-reading Conor Cunningham's "Does Join Order Matter?" recently and found this quote in the post that stood out. As an aside, read this post. It's fantastic.

"...SQL Server’s QP is actually not set up to give you the best plan.  Instead, it is set up to give you a good enough plan quickly (where good enough is very close to “best”)."

I get that, and it makes sense. Why spend 4 hours optimizing a query that runs in 2 minutes to get it down to 1:50 (Conor's example) when the optimizer can spend 1 sec and have it execute in those two minutes. If the optimizer looked for the best plan, I'd be very angry as a customer. Especially if this happened with any frequency.

Or would I?

I'm not suggesting that we ought to optimize every query, or that I'd prefer the optimizer didn't try to normalize queries, or that I want to see the internal tree and try to figure out if I can rewrite the query. I might, however, want to do that for some queries. There are queries that I might want to find the "best" plan for, either because they take a long time to run, or they are run often. I could then use that in something like a USE PLAN.

Computers are cheap these days, as is disk. If I were to somehow able to copy my database onto an instance of SQL Server on a cheap PC, wouldn’t it be cool if I could set that optimizer loose on my query and let it try ALL possible plans? I'd be happy if it spent that 4 hours and gave me back an optimal plan that I could carry back over to my expensive, busy server, and shave that 10sec off a query that I run 10,000 times a day.

I could see some value in a "test" instance of SQL Server that just looked for optimal plans. Now if I could just get Microsoft to build it.