There are a few possible explanations as to why you see this behavior. Some common ones are
- The subquery or CTE may be being repeatedly re-evaluated.
- Materialising partial results into a
#temptable may force a more optimum join order for that part of the plan by removing some possible options from the equation. - Materialising partial results into a
#temptable may improve the rest of the plan by correcting poor cardinality estimates.
The most reliable method is simply to use a #temp table and materialize it yourself.
Failing that regarding point 1 see Provide a hint to force intermediate materialization of CTEs or derived tables. The use of TOP(large_number) ... ORDER BY can often encourage the result to be spooled rather than repeatedly re evaluated.
Even if that works however there are no statistics on the spool.
For points 2 and 3 you would need to analyse why you weren’t getting the desired plan. Possibly rewriting the query to use sargable predicates, or updating statistics might get a better plan. Failing that you could try using query hints to get the desired plan.