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.
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.
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.