Factors analyzed in optimizing queries

Query plans consist of retrieval tactics and an ordered set of execution steps, which retrieve the data needed by the query. In developing query plans, the query optimizer examines:

For each plan, the query optimizer determines the total cost by computing the costs of logical and physical I/Os, and CPU processing. If there are proxy tables, additional network related costs are evaluated as well. The query optimizer then selects the cheapest plan.

Statements in a stored procedure or trigger are optimized when the respective statements are first executed, and the query plan is stored in the procedure cache. If a respective statement is not executed, then it will not be optimized until a later execution of the stored procedure in which the statement is executed. If other users execute the same procedure while an unused instance of a stored procedure resides in the cache, then that instance is used, and previous executed statements in that stored procedure are not recompiled.