What Is a Query Optimizer?

For any given SQL statement, the source tables can be accessed in many ways to build the desired resultset. The query optimizer analyzes all the possible ways that the resultset can be built and chooses the most appropriate method. This method is called the query execution plan. SQL Server uses a cost-based optimizer. The optimizer assigns a cost to every possible execution plan in terms of CPU resource usage and disk I/O. The optimizer then chooses the execution plan with the least associated cost.

Thus, the primary goal of the query optimizer is to find the cheapest execution to minimize the total time to process the query. Because I/O is the most significant factor in query processing time, the optimizer analyzes the query and primarily searches for access paths and techniques to minimize the number of logical and physical page accesses as much as possible. The fewer the number of logical and physical I/Os performed, the faster the query should run.

The process of query optimization in SQL Server Versions 7.0 and 2000 is extremely complicated and is based on sophisticated costing models and data-access algorithms. It is beyond the scope of a single chapter to try to explain in detail all the various costing algorithms that the optimizer currently employs. This chapter is intended to help you better understand some of the concepts related to how the query optimizer chooses an execution strategy and provide an overview of the query optimization strategies employed to improve query processing performance.


SQL Server 7.0 incorporated a completely revamped and more intelligent query optimizer than in previous versions of SQL Server. Beyond some likely bug fixes and enhancements to the algorithms, no significant changes were made to the query optimizer in SQL Server 2000. The only real enhancements to the optimizer in SQL Server are related to indexed views. In SQL Server 2000, the optimizer can consider using an indexed view to process a query in place of the actual table, if the indexed view exists and contains all the needed data. The indexed view does not have to be explicitly specified in the query. For more information on indexed views, see Chapter 34 and Chapter 27, "Creating and Managing Views in SQL Server."

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