Costly Joins, recommendeations needed on inherited system

ryan.branstetter 2018-05-16 15:53:19

Hugo Kornelis 2018-05-17 09:12:10
There's something very funky going on in this execution plan. You might even have stumbled upon a bug. (Or I am overlooking something).

The operator with the highest estimated cost is a Merge Join (Concatenation). This is weird, because a Merge Join operator is normally quite cheap (unless doing a many to many merge join). The Concatenation operation cannot be many to many, so this should be a very cheap operation.

In the underlying plan XML, the costs of this operator (Node ID 228 if you want to check for yourself) shows as EstimateIO="0" EstimateCPU="0.00663493". The estimated total operator cost is not stored in the XML; tools such as SSMS or PE compute this from this node's and the child node's EstimatedTotalSubtreeCost properties. This Merge Join (Concatenation) has EstimatedTotalSubtreeCost="50.516"; the two child nodes have EstimatedTotalSubtreeCost="0.271393" and EstimatedTotalSubtreeCost="15.0862". Doing the math, the total operator cost should be ((50.516 – (0.271393 + 15.0862) = 35.158407. This matches the number that both SSMS and PE show.

To me it is totally unclear how an operator with zero estimated IO cost and 0.0066 estimated CPU cost can have a total estimated cost of over 35. This may in part be related to the fact that some properties are shown on a per-execution basis and others on a total-of-all-executions basis (and I do not yet fully know for each property how it is shown). But at an estimated 22.7 executions, there still is a huge gap between (22.7 * (0 + 0.0066)) estimated IO + CPU version 35 estimated total. If this were a Sort or Hash Match operator I would blame the estimated cost of the memory grant but that doesn't work here either.

Long story short: I suspect that the information you see is misleading. The joins are probably not that expensive at all.

Would it be possible for you to use the Get Actual Plan button in Plan Explorer (preferably with With Live Query Profile enabled) and post the results? Do beware of involuntarily sharing sensitive information; see this article for more information. (And just to be clear: using the anonymize feature will hide all possibly sensitive data, but it almost always also hides everything I'd need to know to assist in tuning the query).

In addition, the sources referenced in the query appear to be views. Can you post the view definitions as well?