O SQL Server não otimiza a junção de mesclagem paralela em duas tabelas particionadas de forma equivalente

Você está certo que o otimizador do SQL Server prefere não gerar MERGEplanos de junção paralelos (essa alternativa custa bastante). Paralelo MERGEsempre requer trocas de reparticionamento em ambas as entradas de junção e, mais importante, requer que a ordem das linhas seja preservada nessas trocas.

O paralelismo é mais eficiente quando cada encadeamento pode ser executado independentemente; a preservação de ordem geralmente leva a esperas frequentes de sincronização e pode, em última análise, fazer com que as trocas se espalhem para tempdbresolver uma condição de impasse dentro da consulta.

Esses problemas podem ser contornados executando várias instâncias de toda a consulta em um encadeamento cada, com cada encadeamento processando um intervalo exclusivo de dados. No entanto, essa não é uma estratégia que o otimizador considera nativamente. Como está, o modelo original do SQL Server para paralelismo quebra a consulta nas trocas e executa os segmentos do plano formados por essas divisões em vários encadeamentos.

Existem maneiras de conseguir a execução de planos de consulta inteiros em vários encadeamentos em intervalos de conjuntos de dados exclusivos, mas eles exigem truques com os quais nem todos ficarão satisfeitos (e não terão suporte da Microsoft ou garantia de funcionamento no futuro). Uma dessas abordagens é iterar sobre as partições de uma tabela particionada e dar a cada thread a tarefa de produzir um subtotal. O resultado é a SUMcontagem de linhas retornada por cada thread independente:

Obter números de partição é bastante fácil a partir de metadados:

DECLARE @P AS TABLE
(
    partition_number integer PRIMARY KEY
);

INSERT @P (partition_number)
SELECT
    p.partition_number
FROM sys.partitions AS p 
WHERE 
    p.[object_id] = OBJECT_ID(N'test_transaction_properties', N'U')
    AND p.index_id = 1;

Em seguida, usamos esses números para conduzir uma junção correlacionada ( APPLY) e a $PARTITIONfunção para limitar cada thread ao número de partição atual:

SELECT
    row_count = SUM(Subtotals.cnt)
FROM @P AS p
CROSS APPLY
(
    SELECT
        cnt = COUNT_BIG(*)
    FROM dbo.test_transaction_item_detail AS i
    JOIN dbo.test_transaction_properties AS t ON
        t.transactionID = i.transactionID
    WHERE 
        $PARTITION.pf_test_transactionId(t.transactionID) = p.partition_number
        AND $PARTITION.pf_test_transactionId(i.transactionID) = p.partition_number
) AS SubTotals;

O plano de consulta mostra uma MERGEjunção sendo executada para cada linha na tabela @P. As propriedades de varredura de índice clusterizado confirmam que apenas uma única partição é processada em cada iteração:

Infelizmente, isso resulta apenas em processamento serial sequencial de partições. No conjunto de dados que você forneceu, meu laptop de 4 núcleos (hiperthreaded para 8) retorna o resultado correto em 7 segundos com todos os dados na memória.

Para que os MERGEsubplanos sejam executados simultaneamente, precisamos de um plano paralelo em que os IDs de partição sejam distribuídos pelos encadeamentos disponíveis ( MAXDOP) e cada MERGEsubplano seja executado em um único encadeamento usando os dados em uma partição. Infelizmente, o otimizador frequentemente decide contra o paralelo MERGEpor motivos de custo e não há nenhuma maneira documentada de forçar um plano paralelo. Existe uma maneira não documentada (e sem suporte), usando o sinalizador de rastreamento 8649 :

SELECT
    row_count = SUM(Subtotals.cnt)
FROM @P AS p
CROSS APPLY
(
    SELECT
        cnt = COUNT_BIG(*)
    FROM dbo.test_transaction_item_detail AS i
    JOIN dbo.test_transaction_properties AS t ON
        t.transactionID = i.transactionID
    WHERE 
        $PARTITION.pf_test_transactionId(t.transactionID) = p.partition_number
        AND $PARTITION.pf_test_transactionId(i.transactionID) = p.partition_number
) AS SubTotals
OPTION (QUERYTRACEON 8649);

Agora, o plano de consulta mostra os números de partição @Psendo distribuídos entre os encadeamentos em uma base de rodízio. Cada thread executa o lado interno da união de loops aninhados para uma única partição, alcançando nosso objetivo de processar dados disjuntos simultaneamente. O mesmo resultado agora é retornado em 3 segundos em meus 8 hipernúcleos, com todos os oito em 100% de utilização.

Não estou recomendando que você use essa técnica necessariamente - veja meus avisos anteriores - mas ela aborda sua pergunta.

Consulte meu artigo Melhorando o desempenho da junção de tabelas particionadas para obter mais detalhes.

Columnstore

Visto que você está usando o SQL Server 2012 (e supondo que seja Enterprise), você também tem a opção de usar um índice columnstore. Isso mostra o potencial de junções de hash no modo de lote onde há memória suficiente disponível:

CREATE NONCLUSTERED COLUMNSTORE INDEX cs 
ON dbo.test_transaction_properties (transactionID);

CREATE NONCLUSTERED COLUMNSTORE INDEX cs 
ON dbo.test_transaction_item_detail (transactionID);

Com esses índices em vigor, a consulta...

SELECT
    COUNT_BIG(*)
FROM dbo.test_transaction_properties AS ttp
JOIN dbo.test_transaction_item_detail AS ttid ON
    ttid.transactionID = ttp.transactionID;

...resulta no seguinte plano de execução do otimizador sem nenhum truque:

Resultados corretos em 2 segundos , mas eliminar o processamento em modo de linha para o agregado escalar ajuda ainda mais:

SELECT
    COUNT_BIG(*)
FROM dbo.test_transaction_properties AS ttp
JOIN dbo.test_transaction_item_detail AS ttid ON
    ttid.transactionID = ttp.transactionID
GROUP BY
    ttp.transactionID % 1;

A consulta de armazenamento de colunas otimizada é executada em 851 ms .

Geoff Patterson criou o relatório de bug Partition Wise Joins , mas foi fechado como Won't Fix.

A maneira de fazer o otimizador funcionar da maneira que você pensa melhor é por meio de dicas de consulta.

Nesse caso,OPTION (MERGE JOIN)

Ou você pode ir até o fim e usarUSE PLAN