What is Index Spool in Execution Plan, is it something that is not desirable, because I have couple of them which are costin 70% of my query.
asked
What is Index Spool?
Whether or not it is desirable is based on the context of the query, the data and the execution plan.
Spools are used as optimization techniques, but can seem costly as they incur writes to tempdb. Avoiding them may make your query performance worse.
Similar to this question about table spools
More info here may help us answer your question
That's one of the more costly operations that can occur. It basically means that the optimizer thought it needed to build it's own index in order to satisfy the query.
In general you need to look at your indexing to see if you've got indexes in the right places, check your statistics to be sure they're up to date, and examine the query itself to be sure that you aren't pasting non-sargeable arguments (functions on columns, that sort of thing) or any other problematic query construct.
For specific answers, you'd need to post the query, the execution plan and, if possible, a sample structure and some data so that we can replicate your issue.
