Knobs for query optimization

As advanced as the query optimization process is, inefficient plans are still a possibility, which is why a database developer can use hints in the T-SQL statement and guide the Query Optimizer toward producing an intended plan. There are several classes of thoroughly documented query hints that affect query optimization, and it is important to call out a few that can be useful when troubleshooting a query performance issue, some of which we will use in upcoming chapters.

Note

Keep in mind that hints force certain behaviors with T-SQL statement optimization and execution. Microsoft recommends that hints are thoroughly tested and only used as a last resort. Hinted statements must be reviewed with every upgrade to a major version to determine if they are still needed since new versions may change behavior, rendering the hint unnecessary or even harmful.

Let’s look at some of the available hints for the Query Optimizer:

• FORCE ORDER...