Por que o SQL Server não usaria um operador Merge Join (Concatenação) em valores não exclusivos, mas pré-classificados?

Isso é essencialmente respondido por Ordem e natureza das colunas na lista de seleção da consulta UNION classificada afeta o desempenho , que se vincula à explicação completa e complicada no meu artigo, Evitando classificações com concatenação de junção de mesclagem .

O principal ponto a ser considerado é que o otimizador de consultas do SQL Server não realiza uma busca exaustiva das estratégias disponíveis para evitar ordenações. Esta é uma decisão pragmática, pois o espaço de busca cresce rapidamente.

Como resultado, às vezes você verá uma classificação evitável em um plano de execução. Existe um método para determinar como o SQL Server atualmente deriva os requisitos de classificação para Concatenação de Mesclagem e como ele pode, às vezes, tomar atalhos (dado um índice ou restrição exclusivos, por exemplo). Ainda assim, isso é observacional; não há garantias comportamentais publicadas.

Nesse sentido, a resposta para esse tipo de pergunta é sempre a mesma e envolve seguir a lógica utilizada pelo otimizador.

A resposta para o seu tipo específico da mesma questão subjacente é abordada na seção do meu artigo, Problemas com índices não exclusivos :

A maneira como o otimizador raciocina sobre os requisitos de classificação para concatenação de mesclagem pode resultar em problemas de classificação desnecessários, como mostra o próximo exemplo:

CREATE TABLE #T1 (c1 int, c2 int, c3 int, c4 int, c5 int);
CREATE TABLE #T2 (c1 int, c2 int, c3 int, c4 int, c5 int);
CREATE CLUSTERED INDEX cx ON #T1 (c1);
CREATE CLUSTERED INDEX cx ON #T2 (c1);

SELECT * FROM #T1 AS T1
UNION ALL 
SELECT * FROM #T2 AS T2
ORDER BY c1
OPTION (MERGE UNION);

DROP TABLE #T1, #T2;

Observando a consulta e os índices disponíveis, esperaríamos um plano de execução que realizasse uma varredura ordenada dos índices agrupados, usando concatenação de junção por mesclagem para evitar a necessidade de qualquer ordenação. Essa expectativa é totalmente justificada, pois os índices agrupados fornecem a ordenação especificada na ORDER BYcláusula. Infelizmente, o plano que obtemos inclui duas ordenações:

Não há uma boa razão para essas classificações; elas só aparecem porque a lógica do otimizador de consultas é imperfeita. A lista de colunas de saída da mesclagem (c1, c2, c3, c4, c5) é um superconjunto de ORDER BY, mas não há uma chave única para simplificar essa lista. Como resultado dessa lacuna no raciocínio do otimizador, ele conclui que a mesclagem requer que sua entrada seja classificada em (c1, c2, c3, c4, c5).

Fornecendo mais uma ilustração para cobrir a importância da lista de projeções:

DECLARE 
    @tenantId uniqueidentifier = NEWID(),
    @object1Id uniqueidentifier = NEWID(),
    @object2Id uniqueidentifier = NEWID();

SELECT 
    SequenceNumber, -- added
    OtherNumber, 
    Payload 
FROM T1 
    WITH (INDEX(IX_TenantId_Object1Id_OtherNumber)) 
WHERE 
    TenantId = @tenantId 
    AND Object1Id = @object1Id

UNION ALL

SELECT 
    SequenceNumber, -- added
    OtherNumber, 
    Payload 
FROM T2 
    WITH (INDEX(IX_TenantId_Object2Id_OtherNumber)) 
WHERE 
    TenantId = @tenantId 
    AND Object2Id = @object2Id
ORDER BY OtherNumber;

Adicionar SequenceNumber ao início da lista de projeção resultou na simplificação do requisito de classificação devido às chaves (primárias) em ( TenantId,SequenceNumber ), com TenantId restrito a um único valor pelo predicado de igualdade em sua consulta.