Index spool

GANGA_dba 2014-08-04 22:10:17

Hi, I have a query which is running for more than 3 minutes. I need to fine tune this for the part of Index spool,please suggest.

Query and execution plan attached.link text

SQLkiwi 2014-08-04 22:57:25
The nonclustered index the eager index spool is building is:

CREATE INDEX IX_Missing 
ON dml.ExtGMOPartnerOrganizationRelationMap
    (ParentMPNID)
INCLUDE
    (ChildMPNID);

The recursive CTE is being fully evaluated twice. Consider writing vCTE once to a temporary table, then referencing that twice in the query.