Yep, the joy of cost based optimizers. I remember in my Oracle days a simmilar query hint ORERED was added in version 9 that became my favorite hint there when the cost based optimizer just couldn't get a clue and pick the right driving table of the query. I was already writing queries joining in what my mind was the natural order to execute it based on my knowledge of the system and data. I remember in the Oracle 8 days there were some third party companies that even required you to use Oracle's rule based optimizer instead of cost based and woudn't support performance issues otherwise.

Even if SQL Server had perfect statistics to work with every time, there'd probably still be some cases it picks the wrong driving table, and we don't have any rule based optimizer to fall back on here.