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.