on September 6, 2016
This is one of those little details that confused me a ton when I was first working with execution plans.
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:
When you hover over that bottom index seek (the inner input), things may look at first like they’re wrong with our nested loop.
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.