Evaluating Execution Plans
At first glance, the comparison of the two unique execution plans reveal that using the Equal comparator operator and the
IN logical operator produce a much simpler execution plan than using the
ANY and
EXISTS logical operators. A closer look reveals that the Equal and
IN operators cause an index scan while the
ANY and
EXISTS operators cause multiple index seeks.
The
ANY and
EXISTS operators introduce an element in the execution plan called nested loops. A nested loop is where a
JOIN, in this case an
INNER JOIN, occurs between multiple data sets.
You'll see a list of information for each icon that's displayed in the execution plan, revealed when you hover your cursor over the icon. Four specific pieces of information are of interest in evaluating query performance:
- Estimated I/O Cost. This number represents the step's cost for data input/output activity.
- Estimated CPU Cost. This number represents the step's cost to the processor(s).
- Estimated Operator Cost. This number represents the step's cost to the Query Optimizer as well as the step's percentage of the query's total cost.
- Estimated Subtree Cost. This number represents the step's total cost to the Query Optimizer and all previous steps.
These numbers can be affected by many factors, including the server environment in which the database resides and the current activity load on the SQL Server.
Estimated I/O Cost
A higher unit value represents a higher cost in the query's execution. For this example, the total I/O cost for all steps in the Equal and
IN operator execution plan was 0.915718 units; while the total I/O cost for all steps in the
ANY and
EXISTS operators was 0.0101157 units.
Note that while the
ANY and
EXISTS logical operators contain Equal comparator operators within their arguments, it is the use of these operators that ultimately affects the performance of the query. In this example, using the Equal operator directly on the
SalesOrderDetail table results in a higher I/O cost than using the Equal operator indirectly through the
Products table as an argument to the
ANY and
EXISTS logical operators.
Estimated CPU Cost
The total CPU cost for all steps in the Equal and
IN execution plan was 0.133606 units; while the total CPU cost for all steps in the
ANY and
EXISTS execution plan was 0.0078319 units. Again, a higher unit value represents a higher cost in the query's execution.
Estimated Operator and Subtree Cost
The estimated operator cost is valuable primarily to identify steps that need attention when tuning the query. This article won't discuss tuning strategies—only the cost of the step's execution. The percentage represented in the estimated operator cost is for the entire execution plan. A high percentage does not necessarily represent high cost; however, if query performance is poor, these high percentage items should be the first steps to review because they will have the most impact.
The subtree cost represents the overall cost to the Query Optimizer for the execution of the step in question. Again, the higher the number, the higher the performance cost to execute.
The execution plan for the Equal and
IN operators had a total subtree cost of 1.04932 units; while the total subtree cost for all steps using the
ANY and
EXISTS operators was 1.166191 units.
Final Evaluation
For the specific queries being evaluated and the environment in which the database resides, the overall performance of the Equal and
IN operators was better than the performance of the
ANY and
EXISTS operators. This performance benefit occurred despite the I/O and CPU numbers stating the opposite, because the subtree cost to the Query Optimizer swung the vote in the other direction.
At this point, for this particular scenario, you've eliminated the
ANY and
EXISTS operators as the best choice; however, the final decision of whether to use the Equal comparator operator or the
IN logical operator comes down to personal preference or anticipation of future considerations.
The keys to making an informed decision to determine best query operators involves considering the effect of indexes on query performance, evaluating the execution plans, and taking the database environment into consideration. The results of such evaluations may prove (or disprove) commonly held rules of thumb, but they're instrumental in determining the operator that will result in optimal performance.
Previous Page
