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
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.