High CPU – high Eager Spool costs

in the far low right there is a TVF that spits out 11mil rows get limited and sorted. We can't see inside the function but if the data could come out sorted and converted to an inline function that would be much better.

as for the spool, the estimates for the TVF would make it my first target.

An actual plan would be much more useful. I know you said it takes a while to run though.

The anonymized plan makes it hard to offer specific suggestions for improvement, but:

The problematic area is the subtree below the Nested Loops Left Semi Join. This is probably an EXISTS clause with a number of UNION ALL expressions.

The Index Spools are certainly suspicious-looking. They create an on-the-fly nonclustered index for each iteration of the loop. The optimizer estimates 184,000 iterations – so (184,000 * 2) nonclustered index creations! If that estimate is off, the real situation could be even worse. If the Seek Predicates on those Index Spools can be incorporated in an permanent index on the base tables, that ought to help a lot.

I will admit I have no idea what the table-valued functions are. XML?

One suggestion: the query plan has the vague shape of queries that often benefit from specific per-execution compilation via OPTION (RECOMPILE). Worth a try, but really I would need a non-anonymized post-execution plan to really help with this.