Why a bad plan isn't necessarily a bug
Everyone that has worked with databases for long enough has run into situations where the query optimizer doesn't select the best possible plan. You may find that you can force SQL to use an index, choose a different join algorithm or join order, or use some other query hint to get a much faster plan. But you're reluctant to try to push through a change in your app in order to compensate for what seems to be a clear SQL Server bug.
We see lots of instances where people have the expectation that if SQL chose a demonstrably inefficient plan, it must be a bug. Unfortunately, it's not quite that simple. At the end of the day, the query processor is just a guessing engine that tries to pick the best possible plan based off statistics that summarize the data in a table. There are some significant limits to how accurately the query optimizer can model the real world when it is costing plans. For starters, the information available to it in statistics only summarizes column data at a fairly high level; it doesn't tell the QO all that there is to know about the distribution of values in a column, or the relationships between data in two or more columns. And there's a tradeoff between the complexity of the QO's modeling rules and the amount of time that it takes to cost the hundreds of thousands of plan possibilities that exist for even modestly complex queries. Even if the QO had full knowledge of everything there was to know about your data, trying to take all of those facts into account when compiling plans would lead to more situations where the optimizer spent more time trying to find that perfect plan than it would have taken to actually execute the query. There are definitely QO bugs out there, but the cases where the QO picks a less-than-ideal plan are not necessarily bugs; some are simply the result of these limits. In fact, our experience is that a majority of bad plan issues fall into the "unfortunate, but by design" bucket.
Dubious? That's OK -- I'll try to illustrate some of the more common "by design" situations where you might see poor plan selections here. Even if you end up taking the perfectly defensible position that the QO should be able to handle some of these situations more gracefully, it'll still benefit you to be able to recognize them.
