Execution plans – important properties

Time I wrote another piece on execution plans (see the first post for the full list)

So, now a quick look at some of the important properties of exec plan operators.

• Estimated number of rows – This is the number of rows that the query optimiser estimates this operator will process. The estimate is based on the statistics and on other data available at time of compilation. It’s possible that this number includes a fraction, due to the way to optimiser does its estimates
• Actual number of rows – This is the actual number of rows that were processed by the operator. This value is calculated by the query processor at execution time. An estimated execution plan will not include this.
• Estimated row size – This is the estimated, average data size of each row passing through this operation. This, along with the number of rows affects the memory allocations necessary and show the size of the data chunk that the query operators are processing.
• Seek predicates – Only present on an index seek, This shows what criteria were used in the index seek.
• Object – Only present on seeks, scans and lookups. This shows the database and table names
• Output list – Only for seeks, scans and lookups. This shows the columns returned by this operation. This can be very useful when trying to reduce bookmark/key/RID lookups
• Predicate – This may be present in seeks or scans. This shows an additional filter done during the seek/scan. On a seek this is a predicate applied after the seek predicate and may indicate that the expression listed is not SARGable, or that the index collumns are not in the optimal order for this predicate.

• Estimated data size – This appears as a property of the data flow between operations. It’s an estimate of the total size of the data at this point in the query execution process.

Next up, a few more exec plan properties