Microsoft® SQL Server 2012 Unleashed

What Is the Query Optimizer?

For any given SQL statement, the source tables can be accessed in many ways to return the desired result set. The Query Optimizer analyzes all the possible ways the result set can be generated and chooses the most appropriate method, called the query plan or execution plan. SQL Server uses a cost-based Query Optimizer. The Query Optimizer assigns a cost to every possible execution plan in terms of CPU resource usage and page I/O. The Query Optimizer then chooses the execution plan with the lowest associated cost.

Thus, the primary goal of the Query Optimizer is to find the least expensive execution plan that minimizes the total time required to process a query. Because I/O is the most significant factor in query ...