Estimated vs. Actual Number of Rows in Nested Loop Operators
One problem with learning to work with plans is that there’s just SO MUCH to look at. And it’s a bit spread out.
So, even when looking at a single tooltip, things can be confusing.
Let’s talk about the nested loop operator, who can be particularly weird to understand.
Meet our nested loop
Here’s a nested loop from a SQL Server Execution plan:
For every row that comes in from the top right index seek, SQL Server goes and does the bottom right index seek. Like this:
I think the best way to explain this was tweeted by Andy Mallon:
I thought the outer table was a pile of crackers. The inner a pile of cheese. And nested loops make snacks.
— Andy Mallon (@AMtwo) August 16, 2016
But when you hover over that bottom index seek (the inner input), things may look at first like they’re wrong with our nested cheese and crackers.
We’re trained early to compare estimated vs actual rows in plans
One of the first things we often learn when we’re looking at plans is that SQL Server uses estimates. And sometimes, those estimates are wrong. At first glance, this looks really wrong– it estimated 11.5 rows, and actually got 20,825 rows!
Similarly, we see these same wrong-looking numbers if we hover over the line between the nested loop operator and the “inner” seek:
Read “estimated number of rows” as the estimate per execution
With a nested loop, you have to remember to also look at the number of executions, and do a little math. The number of executions is on the tooltip of the seek itself, but I often have to do a double take to find it, because it’s so crowded. Here it is:
The estimate here is 11.5692 rows per execution * 2,055.56 executions = 23,782.22598 estimated rows.
And that’s not terribly far off from the 29,969 rows that it ended up reading.
When you see what looks like a bad estimate, take a second look!
Check the estimated number of executions and do a little math. SQL Server may have known exactly what it was doing, after all.