In one of the cool channels us SQLMVPs communicate through the question was raised about why do the execution plan do what they do, and we started to elaborate about tweaks and twerks, and apply hinting in one way or the other. Sometimes you need to do these ninja moves, but usually it's because your statistics are skewed and/or your cached query plan is to old and the data has changed to much. This is usually fixed by updating the statistics or rebuild/reorganize your indexes and updating the statistics. It might be that you need to revamp your indexes too. Maybe wrong cluster key or you need a non-clustered index and include some columns. This if the query you're trying to run is a common query or not. Sometimes even a filtered index might be needed, or sometimes partitioning can be of help. Major changes should only be done if this is a common and re-occuring query.
I'd like to end this post with a lovely quote:
I encourage you to consider updating statistics rather than trying syntactic rewrites. Such things worked on rule-based optimizers but it is a goal of the SQL Server QP to avoid these kinds of gymnastics to the extent possible. Focus on the plans that come out and whether the optimizer has the right info to get you the plan shape you think is best more than the syntax.
-Conor Cunningham
