SQL Server 2017: como funciona o feedback de concessão de memória no modo de lote?

O que é o feedback de concessão de memória?

O Feedback de concessão de memória no modo de lote tenta dimensionar as concessões de memória para consultas, corrigindo as estimativas acima e abaixo.

Quando uma consulta é executada que requer uma concessão de memória, o otimizador solicitará uma concessão de um tamanho que ele acredita que manterá todas as operações de linha na memória.

Coisas que geralmente exigem concessões de memória:

• Classificações
• Junções de hash

Quando muita memória é solicitada e concedida, a simultaneidade pode sofrer como resultado. A memória é um recurso finito, e nem tudo pode usar tudo o tempo todo. Não existe acesso ilimitado a esses recursos.

Quando muito pouca memória é solicitada, as consultas podem se espalhar para o disco. Olha, ninguém quer que suas perguntas se espalhem em qualquer lugar. O disco é terrível.

Como funciona a magia?

Quando um plano que requer uma concessão de memória é executado e armazenado em cache, a memória real necessária para executar o plano é recalculada e as informações do plano são atualizadas adequadamente. No momento, requer a presença de um índice ColumnStore para alcançar o modo de execução em lote.

E se a magia não for tão mágica?

Esse recurso tem um ponto final no qual ele retornará à concessão de memória original. Se as consultas forem executadas e precisarem de recálculo constante, nosso recurso mágico desistirá. Eventualmente. No momento em que escrevo, não tenho todos os detalhes de implementação sobre quando ele será encerrado.

Como saber se está funcionando?

Você pode usar eventos estendidos:

Você também pode observá-lo durante o ajuste de consulta regular em várias execuções no plano de execução real.

Você pode me mostrar um exemplo?

É claro! Aqui está um procedimento armazenado. Sob as circunstâncias certas, ele solicitará uma concessão de memória incorreta.

CREATE OR ALTER PROCEDURE dbo.LargeUnusedGrant (@OwnerUserId INT)
AS 
BEGIN

    SELECT TOP 200 *
    FROM dbo.Posts_cx AS p
    WHERE p.OwnerUserId = @OwnerUserId
    AND p.PostTypeId = 1
    ORDER BY p.Score DESC, p.Id DESC;

END;
GO 

O plano de consulta tem um aviso no operador select.

Tristeza mesmo! Nossa consulta pediu muita memória.

Na segunda execução, o aviso desaparecerá.

Ouvi falar de alguns truques sorrateiros...

Itzik Ben-Gan e Niko Neugebauer apresentaram soluções alternativas para recursos e operadores que exigem execução no modo de lote.

1. Crie um índice ColumnStore filtrado e não clusterizado com uma WHEREcláusula que contém 0 linhas
2. Junte sua tabela a uma tabela temporária com um índice Clustered ColumnStore nela sem linhas usando uma junção esquerda supérflua

Ambos os métodos são soluções válidas para que isso funcione!

Criando um índice ColumnStore não clusterizado vazio:

/*Itzik's Trizik*/
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_helper
    ON dbo.Posts
(
    Id,
    AcceptedAnswerId,
    AnswerCount,
    ClosedDate,
    CommentCount,
    CommunityOwnedDate,
    CreationDate,
    FavoriteCount,
    LastActivityDate,
    LastEditDate,
    LastEditorDisplayName,
    LastEditorUserId,
    OwnerUserId,
    ParentId,
    PostTypeId,
    Score,
    ViewCount,
    IsHot )
    WHERE ( Id = -2147483647 AND Id = 2147483647) -- eez impossible!

Executando uma versão diferente do proc na tabela com o índice vazio:

CREATE OR ALTER PROCEDURE dbo.LargeUnusedGrant_alt1 (@OwnerUserId INT)
AS 
BEGIN

    SELECT TOP 200 *
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId = @OwnerUserId
    AND p.PostTypeId = 1
    ORDER BY p.Score DESC, p.Id DESC;

END;
GO 

Usando o mesmo valor:

EXEC dbo.LargeUnusedGrant_alt1 @OwnerUserId = 8672;
GO 

Primeira execução: concessão de memória triste

Segunda corrida: feliz concessão de memória

Junção à esquerda supérflua:

/*Niko's Triko*/
CREATE OR ALTER PROCEDURE dbo.LargeUnusedGrant_alt2 (@OwnerUserId INT)
AS 
BEGIN

    CREATE TABLE #t1 (Id INT, INDEX cx_whatever CLUSTERED COLUMNSTORE);

    SELECT TOP 200 *
    FROM dbo.Posts AS p
    LEFT JOIN #t1 ON 1 = 1
    WHERE p.OwnerUserId = @OwnerUserId
    AND p.PostTypeId = 1
    ORDER BY p.Score DESC, p.Id DESC;

END;
GO 

EXEC dbo.LargeUnusedGrant_alt2 @OwnerUserId = 8672;
GO 

Primeira execução: concessão de memória triste

Segunda corrida: feliz concessão de memória