A basic execution plan

This is another, long overdue post in the series on reading execution plans. Let’s start with a fairly simple plan, and see what can be seen from it at a quick glance.

The above execution plan is from a simple 2 table query with two filter conditions, one on each table. Reading of the exec plan starts from the right. The operations performed by this query are listed below in the order that they occurred.

1. A nonclustered index seek on the table Customers, costing 10% of the total queries cost
2. A lookup to the clustered index to get columns that are not present in the nonclustered index used in (1). This cost 15% of the query’s total cost
3. A nested loop join to join together the resultsets obtained by operations 1 and 2. The join was 0% of the query’s cost.
4. A index seek on the second table (Invoices) costing 14% of the query’s cost
5. Finally a hash match join to join the results of the two tables together. The hash match was the largest cost for the query, at 61%

A lot more info can be obtained by hovering the mouse over any of the icons or rows.

The popup text over any of the icons shows the estimated and actual rows affected by the operation, the estimated row size, the estimated CPU and IO costs as well as information specific to each operation. This information would be useful for finding the operations with the highest IO and CPU cost in the query. The estimated and actual row count gives an indication as to how well SQL estimated the rows affected by the operation. If the estimate is off, the plan selected may not be the optimal one

The popup text for the arrows show the estimated and actual row count, the estimated row size as well as the estimated total data size.

Not bad for a 5 minute glance at a query’s plan.

In the next post in the series, I’ll go a bit more into detail on the operations that appeared in this query, look at the unique properties that each has and on their impact on queries.