SQL Server 2017: como funcionam as associações adaptativas do modo de lote?

Junções adaptáveis ​​do modo de lote

Para associações adaptáveis ​​de modo em lote, o objetivo é não fixar a opção de associação a um tipo específico em tempo de compilação.

Quando disponíveis, Adaptive Joins permitem que o otimizador escolha entre junções de loops aninhados e junções de hash com base nos limites de linha em tempo de execução.

Neste momento, Merge Joins não são consideradas. A pura especulação é que a necessidade de classificar os dados ou a necessidade de injetar uma classificação no plano adicionaria muita sobrecarga ao alterar o curso de uma consulta.

Quando ocorrem as junções adaptativas do modo de lote?

Neste momento, o processamento de consultas no Modo Lote requer a presença de um índice ColumnStore. Eles também exigem, bem, uma junção e um índice que permite a escolha de um loop aninhado ou uma junção de hash.

Como saber se meu Join é Adaptável?

Os planos de consulta para junções adaptativas são bastante distintos.

O operador Adaptive Join é novo no SQL Server 2017 e atualmente possui as seguintes propriedades nos planos de execução reais.

• Operação Física: Junção Adaptativa

• Tipo de junção real: será correspondência de hash ou loops aninhados

• Adaptive Threshold Rows: indica o ponto de inflexão quando o tipo de junção mudará para Hash Match

• Is Adaptive: True para Adaptive Joins

• Tipo de junção estimado: bastante auto-explicativo!

Em um plano estimado ou armazenado em cache, há muito menos informações:

Mais notavelmente, o tipo de junção real está ausente.

O que interrompe as junções adaptativas do modo de lote?

Para monitorar isso, há uma sessão de evento estendido chamada adaptive_join_skipped, e tem os seguintes motivos para ignorar uma associação adaptativa do modo de lote:

• eajsrExchangeTypeNãoSuportado
• eajsrHJorNLJNotFound
• eajsrInvalidAdaptiveThreshold
• eajsrMultiConsumerSpool
• eajsrOuterCardMaxOne
• eajsrOuterSideParallelMarked
• eajsrUnMatchedOuter

Além disso, as Junções Adaptativas do Modo de Lote podem ser ignoradas por outros motivos. Veja essas duas consultas por exemplo:

/*Selecting just integer data*/
SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

/*Selecting one string column from Users*/
SELECT uc.Id, uc.DisplayName, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

Eles são idênticos, exceto que a segunda consulta seleciona a DisplayNamecoluna, que tem um tipo de NVARCHAR(40).

A junção adaptativa do modo de lote é ignorada para a segunda consulta, mas nenhum motivo é registrado na sessão XE. Parece que os dados de string continuam sendo o inimigo inabalável dos índices ColumnStore.

Existem outros padrões de consulta que não conseguem Adaptive Joins, que também não acionam eventos.

Alguns exemplos:

• CROSS APPLYcom umTOP

• OUTER APPLY

eajsrExchangeTypeNãoSuportado

Uma coisa que acionará esse evento parece ser a presença de um operador Repartição Streams. Nesta consulta, o tipo de particionamento é Hash Match. Agradecimentos especiais ao Ser Celestial Intergaláctico disfarçado de um humilde blogueiro conhecido como Paul White pela bizarra pergunta.

SELECT uc.Id, uc.Reputation, CONVERT(XML, CONVERT(NVARCHAR(10), p.Score)).value('(xml/text())[1]', 'INT') AS [Surprise!]
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2009-08-01'
OPTION ( USE HINT ( 'ENABLE_PARALLEL_PLAN_PREFERENCE' ));
GO 

eajsrHJorNLJNotFound

Nenhuma consulta acionou este XE ainda. O que não funciona:

• Dica de mesclagem

• Os padrões de consulta que excluem o tipo de junção, por exemplo, junções de Hash e Merge requerem pelo menos um predicado de igualdade. Escrevendo uma junção >=e <=não aciona o evento.

eajsrInvalidAdaptiveThreshold

Esse evento pode ser acionado por várias consultas TOP, FAST Ne . OFFSET/FETCHaqui estão alguns exemplos:

SELECT uc.Id, uc.DisplayName, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
INNER JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2008-01-01'
OPTION ( FAST 1);
GO 

SELECT TOP 1 uc.Id, uc.DisplayName, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
INNER JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2008-01-01';
GO 

Em algumas circunstâncias, eles também podem ser acionados por consultas de estilo de paginação:

WITH pages
AS ( SELECT TOP 100 uc.Id, ROW_NUMBER() OVER ( ORDER BY uc.Id ) AS n
     FROM   dbo.Users_cx AS uc ),
     rows
AS ( SELECT TOP 50 p.Id
     FROM   pages AS p
     WHERE  p.n > 50 )
SELECT u.Id, u.Reputation
FROM   pages AS p
JOIN   dbo.Users AS u
ON p.Id = u.Id;

eajsrMultiConsumerSpool

Nenhum padrão de consulta conhecido acionou este evento ainda.

O que não acionou até agora:

• CTEs recursivos

• Conjuntos de agrupamento/cubo/agrupamento

• PIVOT e UNPIVOT

• Funções de janela

eajsrOuterCardMaxOne

Alguns tipos diferentes de consultas acionaram esse evento. Uma junção derivada com um TOP 1e uma junção combinada com uma cláusula WHERE com um predicado de igualdade em uma coluna exclusiva:

SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   (SELECT TOP 1 p2.OwnerUserId, p2.Score FROM dbo.Posts AS p2 ORDER BY Id) AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2009-08-01';
GO 

SELECT p.Id, p.ParentId, p.OwnerUserId
FROM dbo.Posts AS p
JOIN dbo.Users_cx AS uc
ON uc.Id = p.OwnerUserId
WHERE p.Id = 17333;
GO 

eajsrOuterSideParallelMarked

Um tipo de consulta que pode acionar esse evento é um CTE recursivo.

WITH postparent AS 
    (
    SELECT p.Id, p.ParentId, p.OwnerUserId 
    FROM dbo.Posts_cx AS p
    WHERE p.Id = 17333

        UNION ALL

    SELECT p2.Id, p2.ParentId, p2.OwnerUserId
    FROM postparent pp
    JOIN dbo.Posts_cx AS p2
    ON pp.Id = p2.ParentId
    )
    SELECT pp.Id, pp.ParentId, pp.OwnerUserId, u.DisplayName
    FROM postparent pp
    JOIN dbo.Users AS u
    ON u.Id = pp.OwnerUserId
    ORDER BY pp.Id
    OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

A razão aqui parece ser que a parte recursiva do CTE, que causa uma zona serial no plano, não permite a escolha de junção adaptativa do modo de lote.

eajsrUnMatchedOuter

Este é o mais comum e parece ocorrer quando um índice é usado para a junção que não pode suportar uma busca. Por exemplo, esta consulta causa uma pesquisa de chave:

SELECT uc.Id, uc.Reputation, p.Score, p.LastActivityDate 
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20080101'
       AND uc.DisplayName = 'harrymc'
       AND p.Score > 1;

A consulta resultante escolhe uma junção de loops aninhados de modo de linha para executar a pesquisa de chave e as junções de tabela, o que aciona o evento.

Outro exemplo é uma consulta que ignora o índice restrito não clusterizado em favor do PK/CX. Neste caso, o PK/CX não conduz com OwnerUserId, então a única opção de junção é uma Junção de Hash.

Em ambos os casos, o "unmatched outer" parece indicar que o índice escolhido não cobre suficientemente nossa consulta.

SELECT uc.Id, uc.Reputation, p.*
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

As junções adaptativas do modo de lote funcionam com várias junções?

Sim, mas no momento da redação deste artigo, parece haver uma limitação:

A associação de um índice ColumnStore a vários índices de armazenamento de linha produzirá várias associações adaptáveis, enquanto uma associação entre vários índices ColumnStore não será adaptável.

Por exemplo, essas duas consultas

SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
JOIN dbo.Comments AS c
ON c.PostId = p.Id
AND  c.UserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
JOIN dbo.Comments_cx AS c
ON c.PostId = p.Id
AND  c.UserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

A primeira consulta une um índice ColumnStore (em Users) a dois índices Row Store em Posts e Comentários. Isso produz dois operadores Adaptive Join.

A segunda consulta une duas tabelas ColumnStore (Users e Comments) a uma tabela Row Store (Posts) e gera uma Adaptive Join.

Existe alguma sobrecarga nas Associações Adaptativas do Modo em Lote?

Sim, todos os planos de junção adaptativa do modo de lote recebem uma concessão de memória. Isso nem sempre é o caso da junção de loops aninhados, a menos que eles recebam a otimização de pré-busca de loops aninhados. A concessão de memória é para dar suporte a um Hash Join, caso o plano exija um com base nos limites de linha.