Microsoft® SQL Server™ 2008 All-In-One Desk Reference For Dummies®

III.2.2. How the Optimizer Works

Query Optimizer analyzes an executed query and creates several possible query plans for the query. For each query plan, the Optimizer determines the estimated cost to run each query. The query plan with the smallest cost is then used to run the query. The query plans aren't actually run to determine the cost but instead are just estimated. Estimated costs derive from input and output requirements of the query, CPU requirements, memory requirements, and more.

Figure 2-1 shows how the Optimizer works. A query is submitted to the database management system. After the query is parsed (verified that the syntax is correct), it's sent to the Optimizer. The Optimizer analyzes the query and the database to determine different indexes to use and different join methods.

The Optimizer then creates several query plans. For each of these plans, it determines an estimated cost. The query is then compiled using the query plan with the lowest estimated cost — a cost-based optimization.

Figure III.2-1. The Query Optimizer analyzes queries and produces a query plan.

III.2.2.1. The cost of a query

Within SQL Server 2008, query plans are evaluated based on the costs. Costs aren't dollars, but instead refer to time used to access resources. The cost is given in seconds but because cost is an estimated value, it doesn't equate to actual seconds. Instead, think of the ...