Whole queryplan and query: https://www.brentozar.com/pastetheplan/?id=BkgbANxN4
I am struggling with a query that uses a lot of time. The obvious reason for this is wrong estimates of amount of rows. I have tried indexing and updated stats, however I do not seem to address the real problem since the query still goes amazingly slow (I guess wrong index or stats).
What I need are help with interpreting this information so I can help the query to be optimized. What index, which DBCC functions or other built in functions should I run between executions to ensure clean cache and whatnot to avoid using wrong stats and the fresh new one?
Examples would be
DBCC FREEPROCCACHE UPDATE STATISTICS *table*
Another bottleneck of the same query would be these hash matches. The Hash Match at the right most are finished within seconds, while the one to the left seems to struggle with the last 213 rows that uses many minutes. What can I do to find out where the problem lies in these hash matches?
I am also trying to solve multiple memory spills in our batch jobs, where I can only seem to optimize memory spill from a single table.
I have multiple sorts and hash matches where there are rather long probes and residuals including multiple tables or "Expressions" which I assume are either aggregated or set by SSIS packages.
Should I solve the 'first' or 'last' spills first? First are 'top of the three' and last would be closest to leaf nodes (operators). I do also wonder about some operators, mentioned below.
Can you explain what the terms mean regarding the execution plan descriptions:
- Build residual
- Probe residual
- Hash keys probe
I believe I understand the following terms:
Order by: The order of which the operator needs the data in
Output list: Which data the operator are retrieving for output