0

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 + ' -> ' + 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:

enter image description here

Execution plan on QA:

enter image description here

Is there a way to know why plans are different on both server, and how to force QA to follow same plan as DEV?

8
  • 3
    Different data... different statistics...
    – Dale K
    May 28 '20 at 1:19
  • @DaleK it's different plan to compute scale, not just different statistics, see the highlighted box in images
    – Sadiq Khoja
    May 28 '20 at 1:25
  • 3
    Yeah, but data and statistics are what SQL Server used to build the execution plan... so with different data and different statistics you can indeed get a completely different plan.
    – Dale K
    May 28 '20 at 1:27
  • 1
    as DaleK said, the tables and their stats will be different, and this is how the execution plan is built. how many rows do you have in the Items table on each environment? assuming the indexes are the same, the statistics and the row count will create the tipping point between index scans and seeks etc ... May 28 '20 at 2:00
  • 1
    Also: your question text says you changed nvarchar(max) to nvarchar(1024) but the code you posted uses varchar(1024). This could lead to loss of information because varchar data types cannot represent same range of characters as nvarchar data types. May 28 '20 at 3:09
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.