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.
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).
Here is a non-anonymised version, its different as i've been making changes but feel a bit lost.
Any help is appreciated.
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.
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.
Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.
Answers and Comments
query plan x670
plan explorer upload x480
execution plan x123
asked: May 29, 2017 at 02:18 PM
Seen: 80 times
Last Updated: Jun 06, 2017 at 03:51 PM
Execution plan mystery
How can eliminate these two sort that are very expensive ? and reduce cost on index seek
Bad Estimated I/O Cost
estimate much higher than actual
query is very slow
How can I avoid this bad plan
I would like to reduce the access to these tables: fact_input_output, dim_org_v, rap_combo
How to get rid of Hash Join and Sort
Estimated way too less compared to Actual no of rows
Why is it doing a Eager Spool followed by a Lazy Spool?
© 2013 SQL Sentry, Inc. All rights reserved.