In my previous post, I talked about the Actual Number of Rows and Estimated Number of Rows properties, their visual representation in execution plans, and the most important ways in which this can be used.

But life is not always perfect, and Microsoft likes to remind us of that. Sometimes, these properties report values in weird and confusing ways. Sometimes the arrow width, as the visual representation in the execution plan, misleads us.

Let’s look at one such case.

Average per execution versus total of all executions

When looking at the Actual and Estimated Number of Rows properties of an operator, you need to be aware of its location within the execution plan. More specific, you need to know whether the operator is part of a subtree that is the lower (“inner”) input of a Nested Loops operator.

In these subtrees, operators often are executed once for each row returned from, the upper (“outer”) input of that same Nested Loops. The Number of Executions (in an execution plan plus run-time statistics, aka “actual execution plan”) shows how often the operator actually executed; the Estimated Number of Executions, available in all execution plans, shows how many executions the optimizer expected.

The reason this matters is because, for operators that are expected to execute multiple times, the Estimated Number of Rows reported in the execution plan is not the grand total, but the average number of rows estimated to be returned by each single execution. However, the Actual Number of Rows that the same operators report in an execution plan plus run-time statistics does get computed as a grand total over all operators. And this can result in needless confusion.

Imagine looking at an execution plan, zooming in on an operator, and seeing the Actual Number of Rows reported as 25000 and the Estimated Number of Rows as 10. That’s a very big difference, and you would probably be alarmed and start digging to find the root cause of the bad cardinality estimate. But what if I told you that the Estimated Number of Executions was 2500? If you never looked at that property, you would not have known. But when you do look, you now know that the estimate was actually 2500 executions, with 10 rows each, for 25000 rows total; and that the actual number reported is 25000 rows. So the estimate was exactly right and there’s no issue here at all!

I recently already blogged about the improved version of Management Studio that fixes the user interface to prevent this confusion. The words “per execution” or “for all executions” are now added to the property name in the graphical execution plan, severely limiting the potential for confusion. But this is still a user interface change only, it has not (yet?) been ported to other client tools such as Azure Data Studio, and the names and values of the properties in the XML that is actually used to access the execution plan are all unchanged.

And what’s worse, even that newest Management Studio will still mislead you due to this inconsistency in how the properties are used, if you rely on the arrow width. The picture above shows two screenshots, side by side, for the same query. On the left is the execution plan only (aka “estimated execution plan”). On the right is the execution plan plus run-time statistics. As you can see, the width of the arrow, as rendered by Management Studio, is based on the Estimated Number of Rows (or Estimated Number of Rows Per Execution) property in an execution plan only, and on the Actual Number of Rows (or Actual Number of Rows for All Executions). Even though Management Studio now does compute and show the Estimated Number of Rows for All Executions, the width of the arrow is still based on the average for a single execution.

So, looking at the execution plans on the left, you might think that the Key Lookup operator doesn’t produce many rows, given the arrow width. But if you look at the actual numbers, even in the execution plan only, you’ll see that even before execution, this operator is already known, or at least estimated, to produce a huge amount of rows.

Conclusion

The Estimated Number of Rows property is always reported as an average per execution. The Actual Number of Rows property is reported as the total of all executions. That makes it hard to compare the two when the (Estimated) Number of Executions is not one.

And while Management Studio recently improved the labelling of the property, to prevent this confusion, the arrow width can still be misleading. This is easy to fix, and appears to be the logical next step after SSMS 18.5. I have posted this as a suggestion on Microsoft’s feedback site, so if you agree with me, let Microsoft know by voting!

This is, unfortunately, not the only case where the Number of Rows properties and the arrow width can confuse you. Stay tuned for more!