SQL Server--If lógica no procedimento armazenado e o cache do plano

SQL Server 2012 e 2016 Standard: Se eu colocar a lógica if-else em um procedimento armazenado para executar uma das duas ramificações de código, dependendo do valor de um parâmetro, o mecanismo armazena em cache a versão mais recente?

Não, ele armazena em cache todas as versões. Ou melhor, ele armazena em cache uma versão com todos os caminhos explorados, compilados com o primeiro conjunto de variáveis ​​passadas. A estimativa de cardinalidade para todos os planos será feita usando-os. Isso pode ser muito ruim se alguns valores passados ​​forem NULL.

Aqui está uma demonstração rápida, usando o banco de dados Stack Overflow.

Crie um índice:

CREATE INDEX ix_yourmom ON dbo.Users (Reputation) INCLUDE (Id, DisplayName);
GO 

Crie um procedimento armazenado com uma dica de índice que aponta para um índice que não existe, em código ramificado.

CREATE OR ALTER PROCEDURE dbo.YourMom (@Reputation INT)
AS 
BEGIN

    IF @Reputation = 1
    BEGIN
        SELECT u.Id, u.DisplayName, u.Reputation
        FROM dbo.Users AS u WITH (INDEX = PK_Users_Id)
        WHERE u.Reputation = @Reputation;
    END;
    
    IF @Reputation > 1
    BEGIN
        SELECT u.Id, u.DisplayName, u.Reputation
        FROM dbo.Users AS u WITH (INDEX = ix_yourdad)
        WHERE u.Reputation = @Reputation;
    
    END;

END;

Se eu executar esse proc armazenado procurando por Reputation = 1, recebo um erro.

EXEC dbo.YourMom @Reputation = 1;

Msg 308, Level 16, State 1, Procedure YourMom, Line 14 [Batch Start Line 32] O índice 'ix_yourdad' na tabela 'dbo.Users' (especificado na cláusula FROM) não existe.

Se corrigirmos o nome do índice e executarmos novamente a consulta, o plano em cache ficará assim:

Dentro, o XML terá duas referências à @Reputationvariável.

<ColumnReference Column="@Reputation" ParameterDataType="int" ParameterCompiledValue="(1)" />

Um teste um pouco mais simples seria apenas obter um plano estimado para o proc armazenado. Você pode ver o otimizador explorando os dois caminhos:

E se na execução seguinte, o valor do parâmetro for alterado, ele irá recompilar e refazer o cache do procedimento armazenado, porque uma ramificação diferente do código deve ser executada? (Esta consulta é muito cara para compilar.) Obrigado.

Não, ele manterá o valor de tempo de execução da primeira compilação.

Se executarmos novamente com um diferente @Reputation:

EXEC dbo.YourMom @Reputation = 2;

Do plano real :

<ColumnReference Column="@Reputation" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(2)" />

Ainda temos um valor compilado de 1, mas agora um valor de tempo de execução de 2.

No cache do plano, que você pode conferir com uma ferramenta gratuita como a que minha empresa desenvolve, sp_BlitzCache :

O procedimento armazenado foi chamado duas vezes e cada instrução nele foi chamada uma vez.

O que temos então? Um plano em cache para ambas as consultas no procedimento armazenado.

Se você quiser esse tipo de lógica ramificada, precisará chamar procedimentos sub-armazenados:

CREATE OR ALTER PROCEDURE dbo.YourMom (@Reputation INT)
AS 
BEGIN

    IF @Reputation = 1
    BEGIN
        
        EXEC dbo.Reputation1Query;

    END;
    
    IF @Reputation > 1
    BEGIN
        
        EXEC dbo.ReputationGreaterThan1Query;
    
    END;

END;

Ou SQL dinâmico:

DECLARE @sql NVARCHAR(MAX) = N''

SET @sql +=
N'
SELECT u.Id, u.DisplayName, u.Reputation
        FROM dbo.Users AS u '
IF @Reputation = 1
BEGIN
    SET @sql += N' (INDEX = PK_Users_Id)
        WHERE u.Reputation = @Reputation;'
END;


IF @Reputation > 1 
BEGIN

SET @sql += ' WITH (INDEX = ix_yourmom)
        WHERE u.Reputation = @Reputation;'

END;


EXEC sys.sp_executesql @sql;

Espero que isto ajude!