Início / dba / Perguntas / 319327
Accepted
SEarle1986
Asked: 2022-11-08 15:46:06 +0800 CST

É possível que o SQL Server conceda mais memória a uma consulta do que a disponível para a instância

  • 772

Outro dia me perguntaram o que aconteceria se o SQL Server quisesse executar uma única consulta que recebesse mais memória do que a disponível para a instância. Meus pensamentos iniciais eram de que eu poderia ver RESOURCE_SEMAPHOREesperas e a consulta nunca seria iniciada.

Fiz alguns testes para tentar descobrir.

Minha instância inicia em 4000 MB de RAM:

EXEC sys.sp_configure N'max server memory (MB)', N'4000'
GO
RECONFIGURE WITH OVERRIDE
GO

Se eu executar minha consulta (deliberadamente horrível):

USE StackOverflow
SELECT      CONVERT(NVARCHAR(4000), u.DisplayName) AS DisplayName,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp2,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp3
FROM        dbo.Users AS u
            JOIN dbo.Posts p
                ON LTRIM(u.DisplayName) = LTRIM(p.Tags)
WHERE       u.CreationDate >= '2008-12-25'
            AND u.CreationDate < '2010-12-26'
ORDER BY    u.CreationDate;

O plano de execução diz que a memória concedida é de 732.008 KB.

Em seguida, defino a memória disponível para minha instância abaixo desse número e reinicio a instância:

EXEC sys.sp_configure N'max server memory (MB)', N'500' /* a value lower than the previous memory grant */
GO
RECONFIGURE WITH OVERRIDE
GO

Executei a consulta novamente e descobri que ela recebe menos memória do que antes (93.176 KB), mas o plano é, na verdade, uma forma diferente.

Em seguida, executei a consulta novamente e usei uma dica de consulta para forçar o plano original a ver qual memória é concedida:

USE StackOverflow
SELECT      CONVERT(NVARCHAR(4000), u.DisplayName) AS DisplayName,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp2,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp3
FROM        dbo.Users AS u
            JOIN dbo.Posts p
                ON LTRIM(u.DisplayName) = LTRIM(p.Tags)
WHERE       u.CreationDate >= '2008-12-25'
            AND u.CreationDate < '2010-12-26'
ORDER BY    u.CreationDate
OPTION (RECOMPILE, USE PLAN N'<xml here>'

Descobri que a consulta agora usa o plano original, mas obtém uma concessão de memória muito semelhante ao plano compilado (93.168 KB) - plano real forçado

Isso parece refutar minha teoria de que eu veria RESOURCE_SEMAPHOREesperas e sugere que há algum mecanismo que impede o SQL Server de conceder mais memória do que está disponível para uma consulta (o que parece perfeitamente sensato!) Aliás, se eu executar a consulta duas vezes em sessões simultâneas no Configuração de servidor de 500 MB, ambas as sessões RESOURCE_SEMAPHOREaguardam o que parece indefinidamente.

Eu posso ver MaxQueryMemoryno plano que parece ser o que está impedindo o SQL Server de conceder mais memória do que tem disponível para uma consulta.

É possível que uma única consulta receba mais memória do que a disponível para a instância? Se não, MaxQueryMemoryo que faz com que o SQL Server não atribua mais memória do que a disponível e como esse número é calculado?

NB - Meu banco de dados StackOverflow está no nível de compatibilidade 130

sql-server

1 respostas

  1. Best Answer

    Por padrão, o SQL Server permitirá que qualquer consulta use até 25% da memória máxima do servidor para uma concessão de memória, mas, na prática, geralmente está mais perto de 20%.

    O Resource Governor tem algo a dizer sobre isso:

    NOZES

    Mas na minha VM de demonstração com Max Server Memory definido como 90 GB, a maior concessão de memória de consulta única que posso obter é 17 GB, não 22 GB.

    WITH
    c AS 
(
SELECT
    c.*,
    n = 
        ROW_NUMBER() OVER 
        (
            PARTITION BY 
                c.PostId 
            ORDER BY 
                c.Score DESC
        )
FROM dbo.Comments AS c
)
SELECT
    c.*
FROM c
WHERE n = 0;

    Aqui está o plano parcial para isso:

    NOZES

    Usando sp_PressureDetector , você pode obter várias informações sobre o uso de memória para um SQL Server. Para mantê-lo em concessões de memória, é isso que acontece quando executo a consulta acima:

    NOZES

    Não é até que eu execute quatro cópias da consulta que se espera em uma fila no semáforo de recurso:

    NOZES

    Aguarde informações:

    NOZES

    Uma maneira geral de pensar sobre isso é que o SQL Server fornecerá cerca de 75% da memória máxima do servidor para consultas que solicitam concessões.

    Nesse caso, 68 GB de memória estão disponíveis para concessões de 90 GB. Como a quarta consulta teria ultrapassado essa marca de ~ 75%, ela teria que esperar.

    Em geral, a bolsa total mais 50% tem que estar disponível para que a memória seja concedida imediatamente. Caso contrário, ele aguardará, às vezes até que uma concessão mais baixa seja forçada (reveja a forced_grant_countcoluna em sys.dm_exec_query_resource_semaphores)

    Você pode aprender mais detalhadamente sobre como o SQL Server prioriza filas de semáforos e concessões de memória em geral a partir destas fontes:

    Lembre-se de que minha resposta é apenas em referência ao modo de linha tradicional e às cargas de trabalho de armazenamento de linha. Adicionar em modo de lote e índices de armazenamento de coluna mudará alguns comportamentos, particularmente em relação ao respeito pela memória máxima do servidor e o que requer uma concessão de memória.

    Com o modo de linha, os locais mais comuns em que você verá concessões de memória concedidas a consultas são quando os planos de consulta apresentam:

    • Classificações
    • Junções ou agregações de hash
    • Loops aninhados otimizados

    As inserções em índices de armazenamento de colunas solicitarão memória mesmo quando não houver nenhuma classificação de solicitação DML no plano de consulta para realizar a compactação.

    • 16

relate perguntas