New query hints

The SQL Server Query Optimizer does an amazing job of execution plan generation. Most of the time, for most queries, it generates an optimal execution plan. And this is not easy at all. There is a lot of potential to get a suboptimal plan: wrong server configuration, poorly designed databases, missing and suboptimal indexes, suboptimal written queries, nonscalable solutions, and so on. And the query optimizer should work for all those workloads, all over the world, all the time.

Depending on data constellation, it generates suboptimal execution plans sometimes. If the execution of the queries is very important from a business point of view, you have to do something to try to achieve at least an acceptable execution plan. One of the weapons you have for this is hints to the query optimizer. With hints, which are actually instructions, you instruct the query optimizer how to generate the execution plan. You take responsibility or you take part of the responsibility for the execution...