Estimated vs. Actual Number of Rows in Nested Loop Operators

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:

I think the best way to explain this was tweeted by Andy Mallon:

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!

The highlighted numbers look waaaay off

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.

• What is a good resource to learn the basics of execution plans?

• Thanks!

• Wish everything was explained using dinosaurs and or cheese analogies! So much fun!

• Manishkumar1980
September 23, 2016 4:41 am

Superb as always.

In SQL blog world, your article surpasses others good bloggers like brentozar, sqlskills, sqlperform and easy to grasp.

• I took a second look, but the estimated number of rows is still 3 times smaller.

Actual Number of Rows: 16964
Estimated Number of Execution: 1
Estimated Number of Rows: 5167.63

Will this hurt the query’s performance? And how should I deal with this?