No SQL Server, como o paralelismo altera as concessões de memória?

E aí?

Para consultas do SQL Server que exigem memória adicional, as concessões são derivadas para planos seriais. Se um plano paralelo for explorado e escolhido, a memória será dividida igualmente entre as threads.

As estimativas de concessão de memória são baseadas em:

• Número de linhas (cardinalidade)
• Tamanho das linhas (tamanho dos dados)
• Número de operadores simultâneos que consomem memória

Se um plano paralelo for escolhido, haverá alguma sobrecarga de memória para processar trocas paralelas (distribuir, redistribuir e reunir fluxos), mas suas necessidades de memória ainda não são calculadas da mesma maneira.

Operadores que consomem memória

Os operadores mais comuns que pedem memória são

• Classificações
• Hashes (junções, agregações)
• Loops aninhados otimizados

Operadores menos comuns que requerem memória são inserções em índices de armazenamento de coluna. Eles também diferem no fato de que as concessões de memória são atualmente multiplicadas pelo DOP para eles.

As necessidades de memória para Classificações são geralmente muito maiores do que para hashes. As classificações solicitarão pelo menos o tamanho estimado dos dados para uma concessão de memória, pois precisam classificar todas as colunas de resultado pelo(s) elemento(s) de ordenação. Hashes precisam de memória para construir uma tabela de hash, que não inclui todas as colunas selecionadas.

Exemplos

Se eu executar essa consulta, intencionalmente sugerida para DOP 1, ela solicitará 166 MB de memória.

SELECT *
FROM 
     (  
        SELECT TOP (1000) 
               u.Id 
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u
OPTION(MAXDOP 1);

Se eu executar esta consulta (novamente, DOP 1), o plano mudará e a concessão de memória aumentará um pouco.

SELECT *
FROM (  
        SELECT TOP (1000) 
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u
JOIN (
        SELECT TOP (1000) 
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u2
ON u.Id = u2.Id
OPTION(MAXDOP 1);

Existem dois tipos e agora um Hash Join. A concessão de memória aumenta um pouco para acomodar a compilação de hash, mas não dobra porque os operadores de classificação não podem ser executados simultaneamente.

Se eu alterar a consulta para forçar uma junção de loops aninhados, a concessão será duplicada para lidar com as classificações simultâneas.

SELECT *
FROM (  
        SELECT TOP (1000) 
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u
INNER LOOP JOIN ( --Force the loop join
        SELECT TOP (1000) 
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u2
ON u.Id = u2.Id
OPTION(MAXDOP 1);

A concessão de memória dobra porque o Nested Loop não é um operador de bloqueio e o Hash Join é.

O tamanho dos dados é importante

Esta consulta seleciona dados de string de diferentes combinações. Dependendo de quais colunas eu selecionar, o tamanho da concessão de memória aumentará.

A forma como o tamanho dos dados é calculado para dados de string variável é linhas * 50% do comprimento declarado da coluna. Isso é verdade para VARCHAR e NVARCHAR, embora as colunas NVARCHAR sejam duplicadas, pois armazenam caracteres de byte duplo. Isso muda em alguns casos com o novo CE, mas os detalhes não são documentados.

O tamanho dos dados também é importante para operações de hash, mas não no mesmo grau que para Classificações.

SELECT *
FROM 
     (  
        SELECT TOP (1000) 
                 u.Id          -- 166MB (INT)
               , u.DisplayName -- 300MB (NVARCHAR 40)
               , u.WebsiteUrl  -- 900MB (NVARCHAR 200)
               , u.Location    -- 1.2GB (NVARCHAR 100)
               , u.AboutMe     -- 9GB   (NVARCHAR MAX)
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u
OPTION(MAXDOP 1);

Mas e o paralelismo?

Se eu executar esta consulta em diferentes DOPs, a concessão de memória não é multiplicada pelo DOP.

SELECT *
FROM (  
        SELECT TOP (1000) 
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u
INNER HASH JOIN (
        SELECT TOP (1000) 
               u.Id
        FROM dbo.Users AS u
        ORDER BY u.Reputation
     ) AS u2
ON u.Id = u2.Id
ORDER BY u.Id, u2.Id -- Add an ORDER BY
OPTION(MAXDOP ?);

Há pequenos aumentos para lidar com mais buffers paralelos por operador de troca, e talvez haja razões internas para que as compilações de Sort e Hash exijam memória extra para lidar com DOP mais alto, mas claramente não é um fator multiplicador.