Same query but different plan on different server I have recursive CTE query, it was taking 30s to execute, after investigation I found out that a column (Name) with datatype nvarchar(MAX) was the problem so I changed it to nvarchar(1024). After which execution time reduced to 2s on DEV env. However, when I perform same alter on QA environment I don't see any improvement. Strange thing is that both servers are generating different execution plan, both server has same configuration, same schema. In fact DEV has more data.

Query:

WITH Ancestor (Id, TreeLevel, Branch, Name) AS 
(
    SELECT 
        I.Id, 0 AS TreeLevel, 
        CAST(I.Name AS VARCHAR(1024)) AS Branch, 
        I.Name
    FROM      
        dbo.Items AS I 
    WHERE   
        (I.Parent_Id IS NULL)

    UNION ALL

    SELECT 
        D.Id, A.TreeLevel + 1 AS TreeLevel, 
        CAST(A.Branch + ' -&gt; ' + CAST(D.Name AS VARCHAR(1024)) AS VARCHAR(1024)) AS Branch, 
        D.Name
    FROM     
        dbo.Items AS D 
    INNER JOIN
        Ancestor AS A ON A.Id = D.Parent_Id 
    WHERE  
        (D.Parent_Id IS NOT NULL)
)
SELECT 
    A.Id, A.TreeLevel, A.Branch, A.Name
FROM     
    Ancestor AS A 


Execution plan on DEV:



Execution plan on QA:



Is there a way to know why plans are different on both server, and how to force QA to follow same plan as DEV?
Solved the problem by altering the index on parent_id

CREATE NONCLUSTERED INDEX [IX_Parent_Id] ON [Items] ([Parent_Id]) INCLUDE (Name) WITH DROP_EXISTING