Concered with the Est Rows vs Actual Row Count

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.

darklea.pesession (11.7 kB)
avatar image By Tennim 1 asked May 29, 2017 at 02:18 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ May 31, 2017 at 12:10 AM

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).

avatar image Tennim Jun 01, 2017 at 12:01 PM

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.


avatar image Aaron Bertrand ♦ Jun 05, 2017 at 08:53 PM

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.

avatar image Tennim Jun 06, 2017 at 02:39 PM

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.

10|10000 characters needed characters left

0 answers: sort voted first
Be the first one to answer this question
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

We are Moving!


Follow this question



asked: May 29, 2017 at 02:18 PM

Seen: 149 times

Last Updated: Jun 06, 2017 at 03:51 PM