The SQL Server Query Optimizer does an amazing job of execution plan generation. Most of the time, for most of the 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 more. And the Query Optimizer should work for all those workloads, all over the world, all the time.
Depending on data constellation, Query optimizer 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...
