Search code examples
sqlsql-serverparameter-sniffing

sql execution latency when assign to a variable


The following query will be ran in about 22 seconds:

DECLARE @i INT, @x INT
SET    @i = 156567

SELECT 
TOP 1
    @x = AncestorId
FROM 
    dbo.tvw_AllProjectStructureParents_ChildView a
WHERE 
    ProjectStructureId = @i AND
        a.NodeTypeCode = 42 AND
        a.AncestorTypeDiffLevel = 1
OPTION (RECOMPILE)

The problem is with variable assignment (indeed this line: @x = AncestorId). when removing the assignment, it speeds up to about 15 miliseconds! I solved it with inserting the result to a temp table but I think it is a bad way.

Can anyone help me what the source of problem is?!

P.S.

bad Execution plan (22s) : https://www.brentozar.com/pastetheplan/?id=Sy6a4c9bW

good execution plan (20ms) :https://www.brentozar.com/pastetheplan/?id=Byg8Hc5ZZ


Solution

  • When you use OPTION (RECOMPILE) SQL Server can generally perform parameter embedding optimisation.

    The plan it is compiling is single use so it can sniff the values of all variables and parameters and treat them as constants.

    A trivial example showing the parameter embedding optimisation in action and the effect of assigning to a variable is below (actual execution plans not estimated).

    DECLARE @A INT = 1, 
            @B INT = 2,
            @C INT;
    
    SELECT TOP (1) number FROM master..spt_values WHERE @A > @B;
    SELECT TOP (1) number FROM master..spt_values WHERE @A > @B OPTION (RECOMPILE);
    SELECT TOP (1) @C = number FROM master..spt_values WHERE @A > @B OPTION (RECOMPILE);
    

    The plans for this are below

    enter image description here

    Note the middle one does not even touch the table at all as SQL Server can deduce at compile time that @A > @B is not true. But plan 3 is back to including the table in the plan as the variable assignment evidently prevents the effect of OPTION (RECOMPILE) shown in plan 2.

    (As an aside the third plan is not really 4-5 times as expensive as the first. Assigning to a variable also seems to suppress the usual row goal logic where the costs of the index scan would be scaled down to reflect the TOP 1)

    In your good plan the @i value of 156567 is pushed right into the seek in the anchor leg of the recursive CTE, it returned 0 rows and so the recursive part had to do no work.

    enter image description here

    In your bad plan the recursive CTE gets fully materialised with 627,393 executions of the recursive sub tree and finally the predicate is applied on the resulting 627,393 rows (discarding all of them) at the end

    enter image description here

    I'm not sure why SQL Server can't push the predicate with a variable down. You haven't supplied the definitions of your tables - or the view with the recursive CTE. There is a similar issue with predicate pushing, views, and window functions though.

    One solution would be to change the view to an inline table valued function that accepts a parameter for mainid and then add that in to the WHERE clause in the anchor part of the definition. Rather than relying on SQL Server to push the predicate down for you.