Comentários de concessão de memória

Aplica-se a: SQL Server 2017 (14.x) e versões posteriores, Instância Gerenciada de SQL do Azure, Banco de Dados SQL do Azure

Às vezes, a consulta é executada com uma concessão de memória grande demais ou pequena demais. Se a concessão de memória for grande demais, inibiremos o paralelismo no servidor. Se for pequena, podemos despejar no disco, o que é uma operação dispendiosa. O feedback de concessão de memória tenta lembrar as necessidades de memória de uma execução anterior (com feedback percentílico, várias execuções anteriores). Com base nessas informações de consultas históricas, o feedback de concessão de memória ajusta a concessão dada à consulta de acordo com as execuções subsequentes.

Esse recurso foi lançado em três ondas. Feedback de concessão de memória no modo em lotes, seguido por feedback de concessão de memória no modo em linhas e, no SQL Server 2022 (16.x), introduzimos a persistência de feedback de concessão de memória no disco usando o Repositório de Consultas e um algoritmo avançado conhecido como concessão de percentil.

Feedback de concessão de memória no modo em lotes

Aplica-se a: SQL Server (a partir do SQL Server 2017 (14.x)), Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure (a partir do nível de compatibilidade de banco de dados 140)

Um plano de execução da consulta inclui a memória mínima necessária para execução e o tamanho ideal de concessão de memória para que todas as linhas caibam na memória. Desempenho é prejudicado quando os tamanhos de concessão de memória são dimensionados incorretamente. Concessões excessivas resultam em desperdício de memória e em redução de simultaneidade. Concessões de memória insuficientes causam despejos dispendiosos no disco. Lidando com cargas de trabalho repetitivas, os comentários de concessão de memória de modo de lote recalcula a memória real necessária para uma consulta e atualiza o valor de concessão do plano armazenado em cache. Quando uma instrução de consulta idêntica for executada, a consulta usará o tamanho de concessão de memória revisado, reduzindo concessões de memória excessivas que afetam a simultaneidade e corrigindo concessões de memória subestimadas que causam despejos dispendiosos no disco.

O gráfico a seguir mostra um exemplo de uso dos comentários de concessão de memória adaptável de modo de lote. Na primeira execução da consulta, a duração foi de 88 segundos devido à grande quantidade de despejos:

DECLARE @EndTime datetime = '2016-09-22 00:00:00.000';
DECLARE @StartTime datetime = '2016-09-15 00:00:00.000';

SELECT TOP 10 hash_unique_bigint_id
FROM dbo.TelemetryDS
WHERE Timestamp BETWEEN @StartTime AND @EndTime
GROUP BY hash_unique_bigint_id
ORDER BY MAX(max_elapsed_time_microsec) DESC;

Com os comentários de concessão de memória habilitado, na segunda execução, a duração é de 1 segundo (reduzido dos 88 segundos), os despejos são totalmente removidos e a concessão é maior:

Dimensionamento de comentários de concessão de memória

Para uma condição de concessão de memória excessiva, se a memória concedida for mais de duas vezes o tamanho da memória real usada, os comentários de concessão de memória recalcularão a concessão de memória e atualizarão o plano armazenado em cache. Os planos com concessões de memória abaixo de 1 MB não serão recalculados devido a excedentes.

Para uma condição de concessão de memória de tamanho insuficiente que resulta em despejo no disco de operadores de modo em lotes, o feedback de concessão de memória acionará o recálculo da concessão de memória. Os eventos de despejo são relatados ao feedback de concessão de memória e podem ser exibidos por meio do evento estendido spilling_report_to_memory_grant_feedback. Esse evento retorna a ID do nó do plano e o tamanho dos dados despejados desse nó.

A concessão de memória ajustada é mostrada no plano real (pós-execução) pela propriedade GrantedMemory.

Você pode ver essa propriedade no operador raiz do plano de execução gráfico ou na saída XML do plano de execução:

<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="10336" RequiredMemory="1024" DesiredMemory="10336" RequestedMemory="10336" GrantWaitTime="0" GrantedMemory="10336" MaxUsedMemory="9920" MaxQueryMemory="725864" />

Para que as suas cargas de trabalho sejam qualificadas automaticamente para esse aprimoramento, habilite o nível de compatibilidade 140 para o banco de dados.

Exemplo:

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;

Comentários de concessão de memória e cenários sensíveis a parâmetro

Diferentes valores de parâmetros também podem exigir diferentes planos de consulta para continuarem sendo ideais. Esse tipo de consulta é definido como "sensível a parâmetro".

Para planos sensíveis a parâmetro, os comentários de concessão de memória serão desabilitados em uma consulta se ela tiver requisitos de memória instáveis. O recurso de feedback de concessão de memória é desabilitado após várias execuções da consulta repetidas, e isso pode ser observado pelo monitoramento do evento estendido memory_grant_feedback_loop_disabled. Essa condição é atenuada com o modo de persistência e de percentil para feedback de concessão de memória introduzido no SQL Server 2022 (16.x). O recurso de persistência do feedback de concessão de memória requer que o Repositório de Consultas esteja habilitado no banco de dados e definido como modo de “leitura/gravação”.

Veja mais informações sobre a detecção de parâmetro e a sensibilidade de parâmetro no Guia de arquitetura de processamento de consultas.

Armazenamento em cache dos comentários de concessão de memória

Os comentários podem ser armazenados no plano em cache para uma única execução. Porém, são as execuções consecutivas dessa instrução que se beneficiam dos ajustes de feedback de concessão de memória. Esse recurso aplica-se à execução repetida de instruções. Os comentários de concessão de memória vão alterar somente o plano armazenado em cache. Antes do SQL Server 2022 (16.x), as alterações não eram capturadas no Repositório de Consultas.

O feedback não persistirá se o plano for removido do cache. O Feedback também será perdido se houver failover. Uma instrução que usa OPTION (RECOMPILE) cria um plano e não o armazena em cache. Como ele não é armazenado em cache, nenhum feedback de concessão de memória é produzido, e ele não é armazenado para essa compilação e execução. Porém, se uma instrução equivalente (ou seja, com o mesmo hash de consulta) que não usou OPTION (RECOMPILE) for armazenada em cache e executada novamente, a segunda e a última execuções consecutivas poderão se beneficiar do feedback de concessão de memória.

Acompanhar a atividade de feedback de concessão de memória

É possível controlar eventos de feedback de concessão de memória usando o evento estendido memory_grant_updated_by_feedback. Este evento acompanha o histórico de contagem de execução atual, o número de vezes que o plano foi atualizado por comentários de concessão de memória, a concessão de memória adicional ideal antes da modificação e a concessão de memória adicional ideal depois que os comentários de concessão de memória modificaram o plano armazenado em cache.

Comentários de concessão de memória, administrador de recursos e dicas de consulta

A memória real concedida cumpre o limite de memória de consulta determinado pela dica de consulta ou pelo administrador de recursos.

Desabilitar feedback de concessão de memória no modo em lotes sem alterar o nível de compatibilidade

Comentários de concessão de memória podem ser desabilitados no escopo do banco de dados ou da instrução, mantendo o nível de compatibilidade do banco de dados como 140 e superior. Para desabilitar o feedback de concessão de memória no modo em lotes para todas as execuções de consulta originadas do banco de dados, execute as instruções SQL abaixo dentro do contexto do banco de dados aplicável:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Quando habilitada, essa configuração aparecerá como habilitada em sys.database_scoped_configurations.

Para reabilitar o feedback de concessão de memória no modo em lotes para todas as execuções de consulta originadas do banco de dados, execute isto dentro do contexto do banco de dados aplicável:

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

Também é possível desabilitar os comentários de concessão de memória em modo de lote para uma consulta específica designando DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK como uma dica de consulta USE HINT. Por exemplo:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK'));

Uma dica de consulta USE HINT tem precedência sobre uma configuração no escopo do banco de dados ou uma configuração de sinalizador de rastreamento.

Comentários de concessão de memória do modo de linha

Aplica-se a: SQL Server (a partir do SQL Server 2019 (15.x)), Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure (a partir do nível de compatibilidade de banco de dados 150)

Os comentários de concessão de memória de modo de linha expande o recurso de comentários de concessão de memória do modo de lote, ajustando os tamanhos de concessão de memória para operadores de modo de lote e de linha.

Para habilitar o feedback de concessão de memória no modo em linhas no banco de dados SQL do Azure, habilite o nível de compatibilidade do banco de dados 150 ou superior para o banco de dados ao qual você está conectado ao executar a consulta.

Exemplo:

ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;

Assim como acontece com feedback sobre concessão de memória no modo em lotes, a atividade de feedback de concessão de memória no modo em linhas é visível usando o XEvent memory_grant_updated_by_feedback. Também estamos introduzindo dois novos atributos de plano de execução de consulta para melhorar a visibilidade do estado atual de uma operação de feedback de concessão de memória para o modo em linhas e em lotes.

O feedback de concessão de memória não requer Repositório de Consultas, mas os aprimoramentos de persistência introduzidos no SQL Server 2022 (16.x) exigem que o Repositório de Consultas seja habilitado no banco de dados e no estado de leitura/gravação. Veja mais informações sobre persistência em Feedback de concessão de memória no modo de percentil e de persistência mais adiante neste artigo.

A atividade de feedback de concessão de memória no modo em linhas é visível por meio do evento estendido memory_grant_updated_by_feedback.

Começando com os comentários sobre concessão de memória no modo de linha, dois novos atributos do plano de consulta são mostrados para os planos pós-execução reais: IsMemoryGrantFeedbackAdjusted e LastRequestedMemory, que são adicionados ao elemento XML do plano de consulta MemoryGrantInfo.

• O atributo LastRequestedMemory mostra a memória concedida em KB (quilobytes) na execução de consulta anterior.
• O atributo IsMemoryGrantFeedbackAdjusted permite que você verifique o estado dos comentários sobre concessão de memória para a instrução em um plano de execução de consulta real.

Os valores apresentados nesse atributo são os seguintes:

Feedback de concessão de memória no modo de percentil e de persistência

Aplica-se a: SQL Server (a partir do SQL Server 2022 (16.x)), Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure, somente persistência)

Esse recurso foi introduzido no SQL Server 2022 (16.x), mas esse aprimoramento de desempenho está disponível para consultas que operam no nível de compatibilidade do banco de dados 140 (introduzido no SQL Server 2017) ou superior, ou na dica QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n de 140 e superior, e quando o Repositório de Consultas está habilitado no banco de dados e está no estado de “leitura/gravação”.

• O feedback de concessão de memória de percentil é habilitado por padrão no SQL Server 2022 (16.x), mas não tem efeito se o Repositório de Consultas não estiver habilitado ou quando o Repositório de Consultas não estiver no estado de “leitura/gravação”.
• A persistência para feedback de concessão de memória, da CE e do DOP é habilitada por padrão no SQL Server 2022 (16.x), mas não tem efeito quando o Repositório de Consultas não está habilitado ou quando o Repositório de Consultas não está no estado de “leitura/gravação”.
• O percentil e a persistência para feedback de concessão de memória estão disponíveis no banco de dados SQL do Azure e habilitados por padrão em todos os bancos de dados, existentes e novos.
• O percentil e a persistência para feedback de concessão de memória não estão disponíveis atualmente na Instância Gerenciada de SQL do Azure.

É recomendado ter uma linha de base de desempenho da carga de trabalho antes que o recurso seja habilitado para o banco de dados. Os números de linha de base ajudarão a determinar se o benefício pretendido do recurso está sendo obtido.

O MGF (feedback de concessão de memória) é um recurso existente que ajusta o tamanho da memória alocada para uma consulta com base no desempenho anterior. No entanto, as fases iniciais desse projeto armazenavam apenas o ajuste de concessão de memória com o plano no cache. Quando um plano era removido do cache, o processo de comentários precisava começar novamente, resultando em um desempenho ruim nas primeiras vezes em que a consulta era executada após a remoção. A nova solução é manter as informações de concessão com as outras informações de consulta no Repositório de Consultas para que os benefícios persistam entre as remoções de cache. A persistência e o percentil dos comentários de concessão de memória solucionam as limitações existentes desse recurso de modo não intrusivo.

Além disso, os ajustes de tamanho de concessão eram considerados apenas para a concessão usada por último. Portanto, se a consulta parametrizada ou carga de trabalho exigia tamanhos de concessão de memória bastante variados em cada execução, as informações da concessão mais recente podiam ser imprecisas. Isso pode ser inadequado às necessidades reais da consulta executada. O feedback de concessão de memória nesse cenário não é útil para desempenho porque estamos sempre ajustando a memória com base no último valor de concessão usado. A próxima imagem mostra o comportamento possível com o feedback de concessão de memória sem modo de percentil e de persistência.

Como você pode ver, nesse comportamento de consulta incomum, mas possível, a oscilação entre as quantidades de memória reais necessárias e as concedidas resulta em memória desperdiçada e insuficiente se a própria execução da consulta alternar em termos da quantidade de memória. Nesse cenário, o feedback de concessão de memória é desabilitado sozinho, reconhecendo que está sendo mais prejudicial do que benéfico.

Usando um cálculo baseado em percentil sobre o histórico recente da consulta, em vez de simplesmente a última execução, podemos suavizar os valores de tamanho de concessão com base no histórico de uso de execução anterior e tentar otimizar para minimizar despejos. Por exemplo, a mesma carga de trabalho alternada veria este comportamento de concessão de memória:

O otimizador de consulta utiliza um alto percentil de requisitos de dimensionamento de concessão de memória anteriores para execuções do plano armazenado em cache para calcular tamanhos de concessão de memória, usando dados persistentes no Repositório de Consultas. O ajuste de percentil, que executará os ajustes de concessão de memória, se baseia no histórico recente de execuções. Com o tempo, a concessão de memória dada reduz despejos e memória desperdiçada.

A persistência também se aplica ao feedback do DOP e ao feedback da CE.

Habilitar e desabilitar recursos de feedback de concessão de memória

Desabilitar feedback de concessão de memória no modo em linhas sem alterar o nível de compatibilidade

Comentários de concessão de memória de modo de linha podem ser desabilitados no escopo do banco de dados ou da instrução, mantendo o nível de compatibilidade do banco de dados como 150 e superior. Para desabilitar o feedback de concessão de memória no modo em linhas para todas as execuções de consulta originadas do banco de dados, execute as instruções SQL dentro do contexto do banco de dados aplicável:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Para reabilitar os comentários de concessão de memória em modo de linha para todas as execuções de consulta originadas do banco de dados, execute o seguinte dentro do contexto do banco de dados aplicável:

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

Também é possível desabilitar os comentários de concessão de memória em modo de linha para uma consulta específica designando DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK como uma dica de consulta USE HINT. Por exemplo:

SELECT * FROM Person.Address
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT ('DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK'));

Uma dica de consulta USE HINT tem precedência sobre uma configuração no escopo do banco de dados ou uma configuração de sinalizador de rastreamento.

Habilitar persistência e percentil de feedback de concessão de memória

O feedback de persistência e de percentil é habilitado por padrão no banco de dados SQL do Azure e no SQL Server 2022 (16.x).

Use o nível de compatibilidade do banco de dados 140 ou superior para o banco de dados ao qual você está conectado durante a execução da consulta. É possível alterar isso via ALTER DATABASE:

ALTER DATABASE <DATABASE NAME> SET COMPATIBILITY LEVEL = 140; -- OR HIGHER

O Repositório de Consultas precisa ser habilitado para cada banco de dados em que a parte de persistência desse recurso é usada.

Desabilitar percentil

Para desabilitar o percentil do feedback de concessão de memória para todas as execuções de consulta originadas do banco de dados, execute o seguinte dentro do contexto do banco de dados aplicável:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;

A configuração padrão de MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT é ON.

Desabilitar a persistência

Para desabilitar a persistência dos comentários de concessão de memória para todas as execuções de consulta originadas no banco de dados.

Execute o seguinte no contexto do banco de dados aplicável:

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

A desabilitação da persistência dos comentários de concessão de memória também removerá os comentários coletados existentes.

A configuração padrão de MEMORY_GRANT_FEEDBACK_PERSISTENCE é ON.

Considerações para feedback de concessão de memória

Você pode exibir suas configurações atuais ao consultar sys.database_scoped_configurations.

Considerando que os dados de feedback agora persistem no Repositório de Consultas, há certo aumento nos requisitos de uso do Repositório de Consultas.

Erros de concessão de memória baseada em percentil em relação à redução de despejo. Como o recurso não é mais baseado na última execução, mas em uma observação das várias execuções anteriores, pode haver um aumento no uso de memória para cargas de trabalho oscilantes com ampla variação nos requisitos de concessão de memória entre execuções.

A partir do SQL Server 2022 (16.x), quando o Repositório de Consultas para réplicas secundárias está habilitado, o feedback de concessão de memória tem reconhecimento de réplicas para réplicas secundárias em grupos de disponibilidade. O feedback de concessão de memória pode aplicar feedback em uma réplica primária e em uma réplica secundária de formas diferentes. Porém, o feedback de concessão de memória não é persistido em réplicas secundárias e, no failover, o feedback de concessão de memória da réplica primária antiga é aplicado à nova réplica primária. Perde-se todo feedback aplicado à réplica secundária quando ela se torna a réplica primária. Para obter mais informações, consulte Repositório de Consultas para réplicas secundárias.

Conteúdo relacionado

• Processamento inteligente de consultas em bancos de dados SQL
• Recursos detalhados de processamento de consulta inteligente
• RECONFIGURE (Transact-SQL)
• Monitorar e ajustar o desempenho
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)