Plan Explore v2.7 (8.4.33.0) Estimated Row Did Not Multiply the Estimated Execution in Nested Loops Inner Child

kamingbb 2015-08-14 04:28:18

Just download the Plan Explore v2.7 (8.4.33.0) and load the "7.RowsPerExecution.sqlplan" from demo kit, found that the "Estimated Row" shown in "Key Lookup" (node id 50) did not multiply the "Estimated Execution". It does not match what Aaron Bertrand mentioned in http://sqlblog.com/blogs/aaron_bertrand/archive/2011/09/13/a-demo-kit-for-sql-sentry-plan-explorer.aspx Does it not apply to nested loops operation anymore?

Thanks,
Kelvin

SQLkiwi 2015-08-22 11:30:01
It does still apply to nested loops, yes. The original demo 7 contains an error in the detail, which we did notice at the time, but decided to leave in place because it was too hard to fix, and the general feature it was demonstrating was still correct overall, just with a slight wrinkle in the particular case featured. In any case, you should now use the Updated Demo Kit.

In case you're interested, SSMS happens to get the estimate correct (hence no difference seen in PE) because of a separate plan bug with lookups that I wrote about here. In effect, the bug cancels out the usual wrong behaviour with lookups, resulting in a correct estimate (so two wrongs do sometimes make a right!)

This bug messed with PE's normal nested loops rewrite, hence the row counts in the demo 7 graphic are too large. SQL Sentry updated PE to account for the new plan bug as soon as I reported it. The fix was released in build 7.2.42.0, released 31 October 2012.

So there you go, you're not seeing things or going mad ๐Ÿ™‚

kamingbb 2015-08-24 01:57:50
Thank you Paul ๐Ÿ™‚