Diferença entre os atributos 'GrantedMemory' e 'MaxQueryMemory' no Showplan XML

É um instantâneo das mesmas informações que você pode obter de sys.dm_exec_query_resource_semaphores . Acredito que seja target_memory_kbpara o semáforo de recurso regular multiplicado pela porcentagem máxima de concessão de consulta para o grupo do Resource Governor no qual a consulta está sendo executada. Se o RG não estiver ativado, use apenas o padrão de 25%.

Nos servidores que examinei, geralmente vejo cerca de 28% da memória do servidor não disponível para concessões de memória de consulta. Então, depois de executar uma carga de trabalho, a concessão máxima de consulta disponível para uma consulta é 0,25 * 0,72 * MaxServerMemory.

Aqui está uma consulta que você pode executar que termina quase instantaneamente, mas solicita uma grande concessão de memória:

DECLARE @zero INT = 0;

WITH CTE AS (
    SELECT high
    FROM master..spt_values
    WHERE @zero = 1
)
SELECT *
FROM CTE t1
CROSS JOIN CTE t2
CROSS JOIN CTE t3
ORDER BY t1.high + t2.high + t3.high;

Se eu forçar o tempo limite dessa consulta, recebo o seguinte no plano real:

MaxQueryMemorynão muda como resultado do tempo limite. Ele não é afetado diretamente por outras consultas atualmente em execução e usando a memória de consulta. Ele é afetado indiretamente por qualquer coisa que altere KB de memória de destino para o pool de recursos.

Essas informações podem ser úteis se você tiver vários pools e grupos do Resource Governor diferentes e quiser validar se uma consulta vai para o local correto. Ele também pode fornecer contexto para o tamanho total ou configuração de um servidor ao qual você não tem acesso. É outra maneira de saber se uma consulta expirou aguardando uma concessão de memória, mas já existem outras maneiras de fazer isso. O caso de uso pretendido pode ser fornecer mais informações sobre problemas de desempenho causados ​​​​pela memória de destino mudando bastante nos servidores, mas nunca vi isso acontecer pessoalmente. Talvez isso possa acontecer em servidores com várias instâncias do SQL Server em execução?

Cito a mesma atualização do artigo de suporte para expor a memória máxima habilitada para uma única consulta no Showplan XML no SQL Server 2014 ou 2016

MaxQueryMemory em MemoryGrantInfo: quantidade máxima de memória disponível para concessão de consulta individual em KB, se a consulta exigir memória para ser executada.

O que significa quantidade máxima de memória que pode ser concedida à consulta na situação "atual" se a consulta, durante o processamento, solicitar memória. Esse valor é calculado quando a consulta é executada considerando os requisitos de memória de outras consultas em execução e a memória do sistema usada. Isso é útil na solução de problemas de uma consulta lenta que está sendo executada lentamente porque as estatísticas são distorcidas e o otimizador prepara um plano subótimo que força a consulta a solicitar memória diferente do que realmente precisaria durante a execução. Quando ele solicita muito mais do que o solicitado, a consulta se torna super lenta porque não recebeu a memória necessária. Isso também ajuda a entender se há uma pressão de memória no sistema.

GrantedQueryMemoryOTOH significa quantidade total de memória "realmente" concedida em kilobytes. Pode ser NULL se a memória ainda não foi concedida. O MaxQueryMemorysignifica o máximo que pode ser concedido.

Um blog que vale a pena ler. Noções básicas sobre concessões de memória de consulta