Diagnosticar concessões de memória no SQL Server

Um relatório pode ser rápido sozinho e criar uma fila quando vários usuários o executam juntos. Uma possível explicação é a memória de trabalho reservada para ordenações e operações hash. Otimizar apenas a duração isolada pode esconder quanto espaço a consulta retém e quanto tempo outras requisições passam esperando.

Capturar a fila durante o incidente

Colete os dados enquanto o problema ocorre. A consulta abaixo mostra pedidos ativos de memória e informações das requisições atuais. Ela apenas lê dados, mas exige visibilidade de diagnóstico apropriada: VIEW SERVER PERFORMANCE STATE no SQL Server 2022 e posteriores e, normalmente, VIEW SERVER STATE em versões anteriores.

SELECT
    mg.session_id,
    mg.request_id,
    mg.request_time,
    mg.grant_time,
    mg.requested_memory_kb,
    mg.granted_memory_kb,
    mg.required_memory_kb,
    mg.used_memory_kb,
    mg.max_used_memory_kb,
    mg.wait_time_ms,
    r.status,
    r.wait_type,
    r.total_elapsed_time,
    txt.text AS batch_text
FROM sys.dm_exec_query_memory_grants AS mg
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = mg.session_id
   AND r.request_id = mg.request_id
OUTER APPLY sys.dm_exec_sql_text(mg.sql_handle) AS txt
ORDER BY mg.requested_memory_kb DESC;

O texto pertence ao lote inteiro. Uma procedure pode conter diversas instruções além daquela que pediu a concessão. Use as posições da instrução ou o plano para localizar o trabalho responsável. Guarde juntos os identificadores de sessão e requisição; observar a mesma sessão mais tarde não garante que a operação seja a mesma.

Um grant_time ausente indica que o pedido ainda espera a concessão. Compare os kilobytes solicitados e concedidos com o uso atual e o máximo observado. São observações de trabalho em andamento, não um histórico concluído. Uma consulta recém-iniciada pode usar legitimamente uma parcela pequena do espaço reservado. Registre várias amostras com horário.

RESOURCE_SEMAPHORE aponta para espera por memória de execução. RESOURCE_SEMAPHORE_QUERY_COMPILE está relacionado à compilação. Examine também quem já possui concessões grandes. A consulta aguardando pode ser a vítima, enquanto outra ocupa o espaço disponível.

Distinguir três causas

Uma concessão excessiva reserva muito mais do que a execução precisa. Uma insuficiente pode fazer sort ou hash gravar intermediários em tempdb. Uma fila também pode surgir com concessões individuais adequadas quando operações grandes se sobrepõem demais. Um percentual geral de memória não diferencia essas situações.

Imagine um relatório que solicita 600 MB e repetidamente usa só 25 MB. Investigue a estimativa de linhas e sua largura antes dos operadores intensivos em memória. Não classifique a reserva como desperdício por uma única amostra inicial. Confirme no plano real concluído e com os parâmetros que geram grandes resultados.

Para uma consulta com spill, compare as linhas de entrada estimadas e reais do operador. Um join subestimado pode aumentar muito o volume usado pelo hash. Uma projeção larga pode tornar caro um conjunto moderado. Remover colunas desnecessárias antes da ordenação ajuda, desde que a mudança preserve a semântica da consulta.

Um índice que fornece a ordem necessária pode eliminar uma ordenação. Uma pré-agregação na granularidade correta pode reduzir as linhas antes de um join. Nenhuma opção é universal: o índice tem custo de escrita e uma agregação incorreta pode gerar totais convincentes, porém errados.

Medir com a concorrência necessária

Execute simultaneamente a mistura representativa de relatórios, usando parâmetros realistas. Meça requisições concluídas por minuto e latências elevadas além da duração individual. Uma reserva menor pode melhorar a admissão e, ao mesmo tempo, aumentar trabalho em tempdb e piorar a vazão. Uma consulta isolada mais rápida com uma reserva muito maior pode prejudicar suas vizinhas.

Memory Grant Feedback pode ajustar concessões entre execuções nas versões e modos compatíveis. Comportamento e persistência dependem da versão e configuração. Observe os resultados sem assumir tamanho correto na primeira execução, em toda distribuição de parâmetros ou após uma nova compilação.

Evite começar com hints amplos ou aumento geral da memória do servidor. Primeiro identifique se a pressão vem de estimativas, largura, ordenação ou sobreposição excessiva. Escalonar exports pesados pode funcionar melhor que obrigar cada consulta a usar menos memória.

Conclua com evidências de uma janela comparável: menos pedidos aguardando, spills aceitáveis, vazão estável e resultados corretos. Preserve os planos e horários das amostras para explicar se a alteração reduziu demanda, corrigiu estimativas ou apenas moveu a espera para outro lugar.

Referências técnicas: Microsoft Learn: Memory grant diagnostics · Microsoft Learn: sys.dm_exec_query_memory_grants · Microsoft Learn: Memory grant feedback.