The SQL Server optimizer has continuously improved over the years, taking advantage of new techniques and algorithms to improve its capability to find the most efficient execution plan. Understanding how queries are optimized and what information the optimizer uses to generate and select an execution plan will help you write more efficient queries and choose better indexes. To help the optimizer, you should at least try to avoid the types of nonoptimizable SARGs and other common query optimization problems discussed in this chapter.

The majority of the time, the optimizer chooses the most efficient query plan. When it doesn't, it might be because of problems with the query itself, such as poorly written SARGs and other common query performance problems that were discussed in this chapter. Still, on occasion, the optimizer makes the wrong decision, usually because of inaccurate or incomplete information in the index statistics. When you suspect that the optimizer is making the wrong decision, SQL Server provides table and optimizer hints that you can use to override the optimizer's decisions. However, before arbitrarily applying these hints, you should analyze the queries fully to try to determine why the optimizer is choosing a particular plan. To aid you in this effort, SQL Server provides a number of tools to analyze the query plans generated and determine the source of the problem. These tools are described in the next chapter.

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features