Concered with the Est Rows vs Actual Row Count

Tennim 2017-05-29 14:18:04

The attached plan executes in 2/3 seconds so not overly concerned about size, but my business is scaling up so I am wondering if there is something I can do to bring down the estimated amount of reads.

Aaron Bertrand 2017-05-31 00:10:45
Any chance you could provide a non-anonymized actual plan generated from within Plan Explorer? We can see a lot more details about where the estimates are going wrong (currently I can't see any sort of query at all, nor can I tell what aggregate is causing the stream aggregate, where your estimate is double the actual).
Tennim 2017-06-01 12:01:05
Hi Aaron,

Here is a non-anonymised version, its different as i've been making changes but feel a bit lost.

Any help is appreciated.


Aaron Bertrand 2017-06-05 20:53:05
This is an actual plan, but it wasn't generated from within Plan Explorer, which means a lot of useful information is still missing (also the entire query text is missing, which is a SQL Server limitation we can't do anything about, unless you save the whole query in the command text with the .pesession file, or attach the full text separately). One of the nested loop joins estimates 85K rows but only yields 5 in reality, I wonder if you could try materializing the data from that part of the tree into a #temp table first, give the optimizer some breathing room? I'd also try and see what happens to the estimates if you comment out the COUNT(DISTINCT operations temporarily.
Tennim 2017-06-06 14:39:52
I've added another plan, this time from Plan Explorer directly. Again i've tried to make some modifications but now i'm getting a Index Spool.