Além de liberar explicitamente o cache ou exigir recompilações, o que recompila um procedimento armazenado completo no SQL Server 2019?

não muito

Fora o que foi listado na sua pergunta, não há realmente nenhuma causa genérica comum para um procedimento armazenado sempre recompilar um novo plano para cada instrução, pelo menos não para o do seu exemplo.

O SQL Server oferece algumas maneiras diferentes de rastrear recompilações, mas todas elas estão no nível de instrução. Provavelmente o mais fácil de obter respostas são os Eventos Estendidos. Não há nenhum evento para rastrear a recompilação no nível do objeto, que eu encontrei.

SELECT
     d.map_value
FROM sys.dm_xe_map_values AS d
WHERE d.name = 'statement_recompile_cause'
ORDER BY
    d.map_key;

No SQL Server 2022, isso lista 23 motivos para recompilação, incluindo, uh, "Não é uma recompilação".

• Esquema alterado
• Estatísticas alteradas
• Compilação adiada
• Definir alteração de opção
• Tabela temporária alterada
• Conjunto de linhas remoto alterado
• Para permissões de navegação alteradas
• Ambiente de notificação de consulta alterado
• PartitionView alterado
• Opções do cursor alteradas
• Opção (recompilar) solicitada
• Plano parametrizado liberado
• Linearização do plano de teste
• Plano que afeta a versão do banco de dados alterado
• Política de forçamento do plano do Query Store alterada
• Falha ao forçar o plano do Query Store
• Query Store sem plano
• A execução intercalada exigia recompilação
• Não é uma recompilação
• Dicas do Query Store alteradas
• Falha no aplicativo de dicas do Query Store
• Recompilando o Query Store para capturar a consulta do cursor
• Recompilando para limpar o plano do despachante multiplan

no entanto

Estou ciente de algumas coisas que impedem o cache do plano, o que faria parecer que um módulo precisa ser recompilado todas as vezes (embora, na realidade, seja mais como apenas compilar todas as vezes, eu acho).

1. Chaves simétricas

Como Paulo observa em sua postagem:

A segunda opção é incluir uma instrução que marque todo o lote como não armazenável em cache. Declarações adequadas são frequentemente relacionadas à segurança ou de alguma forma sensíveis.

Isso pode parecer impraticável, mas existem algumas atenuações. Primeiro, a instrução sensível não precisa ser executada – ela apenas precisa estar presente. Quando essa condição for atendida, o usuário que executa o lote nem precisa de permissão para executar a instrução confidencial. Observe com atenção que o efeito está confinado ao lote que contém a declaração sensível.

Siga o link para alguns exemplos.

2. Objetos inexistentes

Ainda não tenho um post publicado sobre isso, mas a ideia geral é que quando há uma IFramificação inexplorada em um procedimento armazenado que faz referência a um objeto que não existe, todo o lote não pode ser armazenado em cache.

Sinta-se à vontade para acompanhar a demonstração abaixo. Observe que se você tentar obter um plano estimado para o procedimento armazenado conforme descrito abaixo, ou se executá-lo de uma forma que explore a ramificação com o objeto inexistente, ocorrerá um erro.

/*Create a table if you need to*/
CREATE TABLE 
    dbo.DinnerPlans
(
    id bigint IDENTITY,
    name nvarchar(40) NOT NULL,
    seat_number tinyint NULL,
    is_free bit NOT NULL,
);
GO

/*First example, with an object that doesn't exist*/
CREATE OR ALTER PROCEDURE
    dbo.i_live
(
    @decider bit = NULL
)
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    
    IF @decider = 'true'
    BEGIN
        SELECT
            dp.*
        FROM dbo.DinnerPlans AS dp;
    END;
    
    IF @decider = 'false'
    BEGIN
        SELECT
            whatever.*
        FROM dbo.AnObjectThatDoesntEvenPretendToExist AS whatever;
    END;
    
    IF @decider IS NULL
    BEGIN
        SELECT
            result = 
                'please make a decision.'
    END;
END;
GO 

/*Say goodbye!*/
DBCC FREEPROCCACHE;

/*This runs without an error*/
EXEC dbo.i_live 
    @decider = 'true';

/*But there's no query plan!*/
SELECT
    object_name =
       OBJECT_NAME(deps.object_id, deps.database_id),   
    deps.type_desc,
    deps.last_execution_time,
    deps.execution_count,
    dest.text,
    query_plan =
        TRY_CAST(detqp.query_plan AS xml)
FROM sys.dm_exec_procedure_stats AS deps
OUTER APPLY sys.dm_exec_sql_text(deps.plan_handle) AS dest
OUTER APPLY sys.dm_exec_text_query_plan(deps.plan_handle, 0, -1) AS detqp;
GO 

Isso me faz pensar que deve haver algumas coisas no SQL Server que farão com que procedimentos armazenados inteiros sejam recompilados.

Claro, você pode definir explicitamente o procedimento armazenado para recompilar toda vez que for executado, com a opção recompile assim:

CREATE PROCEDURE FOO
WITH RECOMPILE
AS
BEGIN
   SELECT * INTO #temp1 FROM table1
   INSERT BAR1 SELECT * FROM #temp1
   INSERT BAR2 SELECT * FROM #temp1
END

Opções alternativas também executam o procedimento armazenado com a dica de recompilação:

EXEC FOO WITH RECOMPILE;

Ou chamando explicitamente o procedimento do sistema sp_recompile:

EXEC sp_recompile N'dbo.FOO';