Por que a eliminação de ingresso não funciona com sys.query_store_plan?

A documentação é um pouco enganosa. O DMV é uma visão não materializada e não possui uma chave primária como tal. As definições subjacentes são um pouco complexas, mas uma definição simplificada sys.query_store_plané:

CREATE VIEW sys.query_store_plan AS
SELECT
    PPM.plan_id
    -- various other attributes
FROM sys.plan_persist_plan_merged AS PPM
LEFT JOIN sys.syspalvalues AS P
    ON P.class = 'PFT' 
    AND P.[value] = plan_forcing_type;

Além disso, sys.plan_persist_plan_mergedtambém é uma visualização, embora seja necessário conectar-se através da Conexão de Administrador Dedicado para ver sua definição. Novamente, simplificado:

CREATE VIEW sys.plan_persist_plan_merged AS   
SELECT     
    P.plan_id as plan_id,    
    -- various other attributes
FROM sys.plan_persist_plan P 
    -- NOTE - in order to prevent potential deadlock
    -- between QDS_STATEMENT_STABILITY LOCK and index locks   
    WITH (NOLOCK) 
LEFT JOIN sys.plan_persist_plan_in_memory PM
    ON P.plan_id = PM.plan_id;

Os índices sys.plan_persist_plansão:

╔════════════════════════╦════════════════════════ ══════════════╦═════════════╗
║ index_name ║ index_description ║ index_keys ║
╠════════════════════════╬════════════════════════ ══════════════╬═════════════╣
║ plan_persist_plan_cidx ║ clusterizado, único localizado em PRIMARY ║ plan_id ║
║ plan_persist_plan_idx1 ║ não clusterizado localizado em PRIMARY ║ query_id(-) ║
╚════════════════════════╩════════════════════════ ══════════════╩═════════════╝

Portanto plan_id, é restrito a ser único em sys.plan_persist_plan.

Agora, sys.plan_persist_plan_in_memoryé uma função de streaming com valor de tabela, apresentando uma visão tabular de dados mantidos apenas em estruturas de memória interna. Como tal, ele não possui restrições exclusivas.

No fundo, a consulta que está sendo executada é, portanto, equivalente a:

DECLARE @t1 table (plan_id integer NOT NULL);
DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT 
    T1.plan_id
FROM @t1 AS T1 
LEFT JOIN
(
    SELECT 
        T2.plan_id
    FROM @t2 AS T2
    LEFT JOIN @t3 AS T3 
        ON T3.plan_id = T2.plan_id
) AS Q1
    ON Q1.plan_id = T1.plan_id;

...que não produz eliminação de junção:

Indo direto ao cerne da questão, o problema é a consulta interna:

DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT 
    T2.plan_id
FROM @t2 AS T2
LEFT JOIN @t3 AS T3 
    ON T3.plan_id = T2.plan_id;

...claramente a junção esquerda pode resultar na @t2duplicação de linhas porque @t3não tem restrição de exclusividade em plan_id. Portanto, a junção não pode ser eliminada:

Para contornar isso, podemos dizer explicitamente ao otimizador que não exigimos plan_idvalores duplicados:

DECLARE @t2 table (plan_id integer NOT NULL UNIQUE CLUSTERED);
DECLARE @t3 table (plan_id integer NULL);

SELECT DISTINCT
    T2.plan_id
FROM @t2 AS T2
LEFT JOIN @t3 AS T3 
    ON T3.plan_id = T2.plan_id;

A junção externa para @t3agora pode ser eliminada:

Aplicando isso à consulta real:

SELECT DISTINCT
    T.plan_id
FROM #tears AS T
LEFT JOIN sys.query_store_plan AS QSP
    ON QSP.plan_id = T.plan_id;

Da mesma forma, poderíamos adicionar GROUP BY T.plan_idem vez do DISTINCT. De qualquer forma, o otimizador agora pode raciocinar corretamente sobre o plan_idatributo em todas as visualizações aninhadas e eliminar ambas as junções externas conforme desejado:

Observe que tornar plan_idúnico na tabela temporária não seria suficiente para obter a eliminação de junção, pois não impediria resultados incorretos. Devemos rejeitar explicitamente plan_idvalores duplicados do resultado final para permitir que o otimizador faça sua mágica aqui.