Query is extremely slow due to Lazy Index Spool

Question

On a powerful machine the SQL Server query is running too slowly.

In the execution plan I can see that most of the time spent goes to a "Lazy Index Spooling" process. In the query some aggregate functions are being used for calculation of values.

How can I speed-up the query (machine resources are enough)?

Thanks in advance.

Hyp

SHOW US the query! And the execution plan, please. Thanks. Also: what do your tables look like (columns + their datatypes) and how are they related (foreign keys). How many rows do these tables have?? What indexes are in place?? – Inarticulate

Thanks for answer. Unfortunately, for reasons of confidentiality I cannot post the query. Overall, it's a trivial left join query between 3 tables (every table contains about 1 millions rows) and in the select list there are SUM calculations. In all indexes the JOIN and WHERe columns are specified. I'd just like to know the general ways to avoid the spooling or improving that. – Hyp

There is no general way to avoid spools that I know of. You have to figure out in your specific case why it is there and what to do about it. The reason it is there is because SQL Server thinks your query is faster using a spool. There is an undocumented way to test this by turning of the optimization that adds the spool. DBCC RULEOFF ('BuildSpool');. Don't do this in production and remember to turn it on when you are done. DBCC RULEON ('BuildSpool'); – Dowry