Stored Procedure Generating Multiple Plans for Different Parameters in SQL Server

Question

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.

Answer 1

My first thought was that you are running SQL Server 2022 and hit a new feature called Parameter Sensitive Plan Optimization, where you can have up to three plans per query (based on selectivity, each plan it in itself sensitive to sniffing). But I tested your code with a lower compatibility level for my database, and we still get different plans.

So, next suspect is recompile for each execution. I hooked up below trace:

CREATE EVENT SESSION [Recompiles] ON SERVER 
ADD EVENT sqlserver.sql_statement_recompile

And there you have it. A recompile event, with the recompile_cause being "Statistics changed".

I.e., because you populated a table with enough data, and then do a SELECT, SQL Server will update statistics for you which in turn is generating a recompile.