Chapter 35. Understanding Query Optimization

by Ray Rankins

IN THIS CHAPTER

• What Is a Query Optimizer?

• Query Compilation and Optimization

• Step 1: Query Analysis

• Step 2: Index Selection

• Step 3: Join Selection

• Step 4: Execution Plan Selection

• Reusing Query Plans

• Other Query Processing Strategies

• Parallel Query Processing

• Data Warehousing and Large Database Query Strategies

• Common Query Optimization Problems

• Managing the Optimizer

Query optimization is the process SQL Server goes through to analyze individual queries and determine the best way to process them. To achieve this end, SQL Server uses a cost-based optimizer. As a cost-based optimizer, the optimizer's purpose is to determine the query plan that will access the data with the least amount of processing time in terms of logical and physical I/Os. The query optimizer examines the parsed SQL queries and, based on information about the objects involved (number of pages in the table, types of indexes defined, index statistics, and so on), generates a query plan. The query plan is the set of steps to be carried out to execute the query.

To allow the optimizer to do its job properly, you need to have a good understanding of how the optimizer determines query plans for queries. This will help you to understand what types of queries can be optimized effectively and to learn techniques to help the optimizer choose the best query path . This knowledge will help you write better queries, choose better indexes, and detect potential performance problems.

NOTE

To better understand the concepts presented in this chapter, you should have a reasonable understanding of how SQL Server manages data objects and indexes and how indexes affect performance. If you haven't already read Chapter 33, "SQL Server Internals," and Chapter 34, "Indexes and Performance," I recommend that you review them now.

NOTE

Occasionally throughout this chapter, some graphical execution plans are used to illustrate some of the principles discussed. In the next chapter, "Query Analysis," a more detailed discussion of the graphical showplan output is discussed that describes the various bits of information contained in the execution plans and how to interpret it. In this chapter, the execution plans are provided primarily to give you an idea of what you can expect to see for the different types of queries presented when you are doing your own query analysis.