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.

7 Comments.Leave new

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

Reply
• Thanks!

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

Reply
• 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.

Please keep in writing

Reply
• 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?

Reply
• I’d typically consider that a small variance in the estimate and would think it unlikely to make a difference, however it will vary depending on the size of the plan and the place of this estimate in the plan.

Let’s say that this under-estimate is placed in the top right corner of a complex plan. It may have under-sized joins, memory, and under-costed the whole plan — because a low estimate here could cause a low estimate coming out of a later join, etc etc. If I see spills in an actual plan, or am suspicious that there are massive underestimates in other places, I might test out, “what if you had to act like there was more data?” to see if it made it faster.

If it’s safe to run the query, I might do that by plugging in different values for parameters that have higher estimates, using hints to force different plan behaviors, etc, to see what happens and compare.

Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu