Estimated Row for Constant Scan is 1 but Actuals is a lot more

Buckley 2014-01-27 07:42:54

Hi,

I'm just getting started at reading execution plans so bear with me please 🙂 This is all new for me.

I see 2 Constant Scans that estimate 1 row will be returned but the actual is 13.000
The query performs ok for now (not much data) but I am intrigued by this big difference and want to become good at reading plans.

I read about Constant Scan and I see it as a data structure that is set up for what follows, the compute scalar. But why is the estimation so way off and is this a potential problem?

Added: I did a sp_updatestats to be sure.

SQLkiwi 2014-01-27 10:20:57
Everything below the Merge Interval is a standard construction built by the query optimizer to build dynamic seeks. I wrote about this in detail in the following blog post:

http://sqlblog.com/blogs/paul_white/archive/2012/01/18/dynamic-seeks-and-hidden-implicit-conversions.aspx

The estimate of 1 row for the Constant Scan is per iteration of the nested loops join above it. SSMS shows per-iteration estimates for the inner side of a nested loops join. It shows a sum over all iterations in actual (post-execution) execution plans. This is confusing to most people when they start out reading plans.

Plan Explorer generally multiplies the estimated number of rows by the estimated number of executions below a nested loops join, to make it easier to compare estimated and actual plans. It is hard to be certain given the anonymized plan uploaded, but it seems Plan Explorer may not have applied this calculation correctly for the Merge Interval subtree in this case.

Buckley 2014-01-27 11:04:04
Hello Mr SqlWiki. Thanks for the response. It makes sense but I'm sure I couldn't have figured it out by my own. Thanks a lot fo for your insight.
Was the query plan above anonymized? I'll recheck but I did not press that button I believe in planexplorer. Maybe that way you can confirm if this was correct or incorrect behaviour of planexplorer.
Aaron Bertrand 2014-01-27 14:31:18
Yes, the plan was anonymized – you may have clicked the button inadvertently.
Buckley 2014-01-27 21:57:18
I reuploaded the plan.
Buckley 2014-01-27 21:57:33
Got some time to reupload the plan
Buckley 2014-01-27 21:56:53
I ran the query again and saved it. It should not be anonymized.
Is the expectation that the estimate number of rows is multiplied by the estimated number of executions. I observe that it stays at 1 (the behaviour of ssms).

link text

SQLkiwi 2014-01-27 21:59:31
Yes that would be my expectation. We'll look into this.
Buckley 2014-01-27 22:11:38
So is the 12.988 (actual) vs 1 (estimated) in the screenshot a false alarm then? Is it a bad misinterpretation of what is going on or is the problem real and should I investigate why the estimate is way off further?

alt text

Buckley 2014-02-04 07:19:42
Do you have more information about this?
SQLkiwi 2014-01-31 02:34:30
It's a false alarm. SQL Server does not report the estimated number of executions correctly for the Merge Interval subtree.
Kevin 2014-02-14 18:36:03
Just as an aside, you probably don't need the GROUP BY clause when you're simply returning the single aggregate value. That one clause definitely adds to the complexity of the execution plan.