Slow Execution Time – Actual/Estimated rows disparity
Thanks for any help.
With most queries, there are fairly obvious 'boundaries' where one can do a sensible proportion of the overall task, and save a relatively small amount of data in a temporary table, to be used in later stages. It may seem counter-intuitive, but simplifying things in this way can make life easier for both the optimizer and future humans who will have to maintain this query. Optimizer benefits include more accurate cardinality estimates, and the potential for useful intermediate indexing. Human benefits, aside from the obvious, include the possibility of seeing new data-knowledge optimizations.
If that isn't an option for you (though I strongly encourage you in that direction), there are a few things that stand out to me. You have a number of scans on large tables which produce relatively few rows. These seem like indexing opportunities. For example:
The highlighted scans in that area of the plan all have predicates applied. All the tables have between 5 and 10 million rows. It may be that the predicates are purely bitmap applications – it is hard to be certain about that in an anonymized plan. Even so, the presence of parallel hash join with a bitmap can also be an indicator of missing indexes.
I could go on with guesses and general observations, but there's truly not a great chance of me hitting something you haven't already looked at – and you have the un-anonymized plan to work with.
Ultimately, there's a lot of work being done in that plan, on some reasonably-sized tables. The six-second duration does not seem entirely unreasonable. I would imagine major improvements will require some serious analysis work (with all the plan and schema information available). The end result of that time investment might still be rather fragile, so I would still encourage you in the direction of simplifying the query as outlined previously.