Microsoft® SQL Server™ 2000 Unleashed, Second Edition

Step 4: Execution Plan Selection

At this point in the query optimization process, the optimizer has examined the entire query and estimated the costs of all possible indexes to be used and of the various query-processing strategies. It now needs to choose which plan to pass on to SQL Server for execution.

For a single table query, choosing the best query plan involves choosing the index and query processing strategy that results in the fewest number of logical I/Os performed to process the query on that table.

For a multitable query, choosing the best plan involves not only determining the cheapest index and query processing strategy for each table individually, but also determining the best index strategy in conjunction with the optimal join ...