SQL Server lets you specify these optimization choices:
The order of tables in a join
The number of tables evaluated at one time during join optimization
The index used for a table access
The I/O size
The cache strategy
In a few cases, the optimizer fails to choose the best plan. In some of these cases, the plan it chooses is only slightly more expensive than the "best" plan, so you need to weigh the cost of maintaining these forced choices over the slightly slower performance.
The commands to specify join order, index, I/O size, or cache strategy, coupled with the query-reporting commands like statistics io and showplan, help you determine why the optimizer makes its choices.
WARNING! Use these options with caution. The forced plans may be inappropriate in some situations and cause very poor performance. If you include these options in your applications, be sure to check their query plans, I/O statistics, and other performance data regularly.
These options are generally intended for use as tools for tuning and experimentation, not as long-term solutions to optimization problems.