Stored Procedure Generating Multiple Plans for Different Parameters in SQL Server

2 votes
1 answer
323 views
sql-server stored-procedures parameter-sniffing
                          I have a stored procedure that is producing slightly different plans for different parameters. I would expect it to use the same plan that was generated when it was run the first time. 

    CREATE TABLE myTable (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        Column1 VARCHAR(50),
        Column2 VARCHAR(50),
        Column3 VARCHAR(50),
        Column4 VARCHAR(50),
        Column5 VARCHAR(50),
        Column6 VARCHAR(50),
        Column7 VARCHAR(50),
        Column8 VARCHAR(50),
        Column9 VARCHAR(50),
        Column10 VARCHAR(50)
    )
    
    DECLARE @i INT = 1
    DECLARE @j INT = 1
    DECLARE @distinct_value_count INT = 20
    DECLARE @distinct_value_count_with_more_rows INT = 3
    DECLARE @rows_per_distinct_value INT = (20000 - (@distinct_value_count_with_more_rows * 2000)) / (@distinct_value_count - @distinct_value_count_with_more_rows)
    
    WHILE @i <= @distinct_value_count
    BEGIN
        DECLARE @current_rows_per_value INT = @rows_per_distinct_value
        IF @i <= @distinct_value_count_with_more_rows
        BEGIN
            SET @current_rows_per_value = @rows_per_distinct_value + 2000
        END
        
        SET @j = 1
        WHILE @j <= @current_rows_per_value
        BEGIN
            INSERT INTO myTable (Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10)
            VALUES ('Value' + CAST(@i AS VARCHAR(2)),
                    'Value' + CAST(@j AS VARCHAR(5)),
                    'Value' + CAST(@j + 1 AS VARCHAR(5)),
                    'Value' + CAST(@j + 2 AS VARCHAR(5)),
                    'Value' + CAST(@j + 3 AS VARCHAR(5)),
                    'Value' + CAST(@j + 4 AS VARCHAR(5)),
                    'Value' + CAST(@j + 5 AS VARCHAR(5)),
                    'Value' + CAST(@j + 6 AS VARCHAR(5)),
                    'Value' + CAST(@j + 7 AS VARCHAR(5)),
                    'Value' + CAST(@j + 8 AS VARCHAR(5)))
            SET @j = @j + 1
        END
        
        SET @i = @i + 1
    END
    
    Create NonClustered Index Idx_col on MyTable(Column1)
    sp_recompile 'dbo.MyTable'
    
    Create or Alter Procedure dbo.tmp_testProc(
    @inValue VarChar(50)
    )
    As
    Begin
    
    Set NoCount On
    
    Select Id 
    Into #tmpCol
    From MyTable
    Where Column1 = @inValue
    
    Select t.*
    From MyTable t
    Join #tmpCol tmp
    On t.Id = tmp.Id
    Order by t.Id
    
    End
    
    --Scenario 1
    exec dbo.tmp_testProc @inValue = 'Value1' 
    
    --Scenario 2
    exec dbo.tmp_testProc @inValue = 'Value5'

I'm executing the stored procedure with parameter value = 'Value1' for the first execution and then with 'Value5'. Here are the execution plans for both scenarios:

**Scenario 1 Execution Plan**: https://www.brentozar.com/pastetheplan/?id=r1H-3HONh 

**Scenario 2 Execution Plan**: https://www.brentozar.com/pastetheplan/?id=r1p42SOE3 

I anticipated that the second scenario would employ the same plan as the first, but I have observed that the plan differs. I was under the impression that the optimizer reuses the same plan generated for the initial parameter set for all subsequent executions. Therefore, I am uncertain why the optimizer is creating different plans in this instance. I would appreciate any clarification on this matter. Thank you for your assistance.
                        
Asked by lifeisajourney (751 rep)
May 9, 2023, 10:48 PM
Last activity: May 10, 2023, 06:20 AM
Stored Procedure Generating Multiple Plans for Different Parameters in SQL Server

Related Questions
