Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Aplica-se a: SQL Server 2017 (14.x) e versões posteriores, Instância Gerenciada SQL do Azure, Banco de Dados SQL do Azure
Às vezes, uma consulta é executada com uma alocação de memória demasiado grande ou demasiado pequena. Se a concessão de memória for muito grande, inibimos o paralelismo no servidor. Se for muito pequeno, podemos transferir para o disco, o que é uma operação dispendiosa. O feedback de concessão de memória procura acompanhar as necessidades de memória de uma execução anterior, através de feedback de percentil, considerando várias execuções anteriores. Com base nessas informações de consulta histórica, o feedback de concessão de memória ajusta a concessão dada à consulta de acordo com as execuções subsequentes.
Este recurso foi lançado em três ondas. Os feedbacks de concessão de memória em modo de lote, seguidos por feedbacks de concessão de memória em modo de linha, e o SQL Server 2022 (16.x) introduziu a persistência em disco dos feedbacks de concessão de memória usando o Query Store e um algoritmo aprimorado conhecido como concessão de percentil.
Observação
Para outros recursos de feedback de consulta, consulte Feedback sobre estimativa de cardinalidade (CE) e Feedback sobre grau de paralelismo (DOP).
Feedback de concessão de memória em modo batch
Aplica-se a: SQL Server (Começando com o SQL Server 2017 (14.x)), Banco de Dados SQL do Azure, Instância Gerenciada SQL do Azure (Começando com o nível de compatibilidade de banco de dados 140)
O plano de execução de uma consulta inclui a memória mínima necessária para a execução e o tamanho ideal de concessão de memória para que todas as linhas caibam na memória. O desempenho é prejudicado quando os tamanhos de concessão de memória são dimensionados incorretamente. Concessões excessivas resultam em memória desperdiçada e concorrência reduzida. Concessões de memória insuficientes causam transferências caras para o disco. Ao abordar cargas de trabalho repetidas, o feedback de concessão de memória em modo de lote recalcula a memória real necessária para uma consulta e, em seguida, atualiza o valor de concessão para o plano armazenado em cache. Quando uma instrução de consulta idêntica é executada, a consulta usa 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 derramamentos caros no disco.
O gráfico a seguir mostra um exemplo de uso do feedback de concessão de memória adaptável em modo de lote. Para a primeira execução da consulta, a duração foi de 88 segundos devido a altos vazamentos:
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 o feedback de concessão de memória ativado, para a segunda execução, a duração é de 1 segundo (abaixo de 88 segundos), os vazamentos são removidos totalmente e a concessão é maior:
Dimensionamento do feedback para 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 usada realmente, o feedback de concessão de memória recalculará a concessão de memória e atualizará o plano armazenado em cache. Os planos com concessões de memória inferiores a 1 MB não serão recalculados em caso de excedentes.
Para uma condição de concessão de memória de tamanho insuficiente que resulte em um derramamento para o disco para operadores de modo de lote, o feedback de concessão de memória acionará um recálculo da concessão de memória. Os eventos de derramamento são relatados para feedback de concessão de memória e podem ser revelados através do spilling_report_to_memory_grant_feedback evento estendido. Esse evento retorna a ID do nó do plano e o tamanho dos dados derramados desse nó.
A concessão de memória ajustada aparece no plano real (pós-execução) por meio da propriedade GrantedMemory.
Você pode ver essa propriedade no operador raiz do showplan gráfico ou na saída XML do showplan:
<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="10336" RequiredMemory="1024" DesiredMemory="10336" RequestedMemory="10336" GrantWaitTime="0" GrantedMemory="10336" MaxUsedMemory="9920" MaxQueryMemory="725864" />
Para que suas cargas de trabalho sejam automaticamente qualificadas para essa melhoria, habilite o nível de compatibilidade 140 para o banco de dados.
Exemplo:
ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;
Feedback de concessão de memória e cenários sensíveis a parâmetros
Valores de parâmetros diferentes também podem exigir planos de consulta diferentes para permanecerem ideais. Esse tipo de consulta é definido como "sensível a parâmetros".
Para planos sensíveis a parâmetros, o feedback de concessão de memória desativa-se automaticamente numa consulta SQL caso haja requisitos de memória instáveis. O recurso de feedback de concessão de memória é desativado após várias execuções repetidas da consulta e isso pode ser observado monitorando o memory_grant_feedback_loop_disabled evento estendido. Essa condição é atenuada com o modo de persistência e 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 Query Store seja habilitado no banco de dados e definido para o estado de "leitura e gravação".
Para obter mais informações sobre deteção de parâmetros e sensibilidade a parâmetros, consulte o Guia de arquitetura de processamento de consultas.
Cache de feedback de concessão de memória
Os feedbacks podem ser armazenados no plano em cache para uma única execução. São as execuções consecutivas dessa instrução, no entanto, que se beneficiam dos ajustes de feedback de concessão de memória. Este recurso aplica-se à execução repetida de instruções. O feedback de concessão de memória alterará apenas 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 será mantido se o plano for removido do cache. O feedback também será perdido se houver um failover. Uma instrução usando OPTION (RECOMPILE) cria um novo 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 não é armazenado para essa compilação e execução. No entanto, se uma instrução equivalente (ou seja, com o mesmo hash de consulta) que não utilizou OPTION (RECOMPILE) foi armazenada em cache e, em seguida, executada novamente, a segunda e posteriores execuções consecutivas podem beneficiar-se do feedback de concessão de memória.
Monitorizar a atividade de feedback de concessão de memória
Você pode acompanhar eventos de feedback de concessão de memória usando o memory_grant_updated_by_feedback evento estendido. Esse evento rastreia o histórico de contagem de execução atual, o número de vezes que o plano foi atualizado pelo feedback 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 após o feedback de concessão de memória ter modificado o plano armazenado em cache.
Feedback de concessão de memória, administrador de recursos e dicas de consulta
A memória efetivamente concedida respeita o limite de memória de consulta determinado pelo administrador de recursos ou pelas dicas de consulta.
Desativar o feedback de concessão de memória em modo batch sem alterar o nível de compatibilidade
O feedback de concessão de memória pode ser desativado no escopo do banco de dados ou da instrução, mantendo o nível de compatibilidade do banco de dados 140 e superior. Para desabilitar o feedback de concessão de memória em modo lote para todas as execuções de consultas que se originam do banco de dados, execute as instruções SQL abaixo no 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 no sys.database_scoped_configurations.
Para reativar o feedback de concessão de memória em modo de lote para todas as execuções de consulta originadas do banco de dados, execute as instruções SQL no 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;
Você também pode desativar o feedback de concessão de memória em modo batch 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 USE HINT dica de consulta tem precedência sobre uma configuração de escopo de banco de dados ou uma configuração de sinalizador de rastreamento.
A memória do modo de linha concede feedback
Aplica-se a: SQL Server (Começando com o SQL Server 2019 (15.x)), Banco de Dados SQL do Azure, Instância Gerenciada SQL do Azure (Começando com o nível de compatibilidade de banco de dados 150)
O feedback de concessão de memória do modo de linha expande o recurso de feedback do modo de lote, ajustando os tamanhos de concessão de memória para operadores tanto do modo de linha como do modo de lote.
Para habilitar o feedback de concessão de memória em modo de linha no Banco de Dados SQL do Azure, ative o nível de compatibilidade do banco de dados 150 ou superior para o banco de dados ao qual está ligado ao executar a consulta.
Exemplo:
ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;
Tal como acontece com o feedback de concessão de memória em modo batch, a atividade de feedback de concessão de memória em modo de linhas é visível através do memory_grant_updated_by_feedback XEvent. Estamos também a introduzir dois novos atributos no plano de execução de consulta para uma visibilidade mais clara do estado atual de uma operação de feedback de atribuição de memória tanto para os modos de linha como de lote.
Os feedbacks de concessão de memória não exigem o Repositório de Consultas, no entanto, os aprimoramentos de persistência introduzidos no SQL Server 2022 (16.x) exigem que o Repositório de Consultas esteja habilitado para o banco de dados e em um estado de leitura e escrita. pt-PT: Para obter mais informações sobre persistência, consulte Percentil e modo de persistência de concessão de memória para feedback mais adiante neste artigo.
A atividade de feedback de concessão de memória do modo de linha é visível através do memory_grant_updated_by_feedback evento estendido.
Começando com o feedback de concessão de memória em modo de linha, dois novos atributos de planos de consulta são mostrados nos planos de pós-execução reais: IsMemoryGrantFeedbackAdjusted e LastRequestedMemory, que são adicionados ao elemento XML de MemoryGrantInfo do plano de consulta.
- O
LastRequestedMemoryatributo mostra a memória concedida em Kilobytes (KB) da execução da consulta anterior. - O atributo
IsMemoryGrantFeedbackAdjustedpermite verificar o estado do feedback de concessão de memória para a instrução dentro de um plano de execução de consulta real.
Os valores apresentados neste atributo são os seguintes:
Valor IsMemoryGrantFeedbackAdjusted |
Descrição |
|---|---|
| N.º: Primeira Execução | O feedback de concessão de memória não ajusta a memória para a primeira compilação e execução associada. |
| Não: Concessão precisa | Se não houver descarregamento para o disco e se a instrução utilizar pelo menos 50% da memória concedida, o feedback de concessão de memória não será ativado. |
| Não: Comentários desativados | Se o feedback de concessão de memória for continuamente acionado e flutuar entre as operações de aumento e diminuição de memória, o mecanismo de banco de dados desativará o feedback de concessão de memória para a instrução. |
| Sim: Ajustando | O feedback de concessão de memória foi aplicado e poderá ser ajustado para a próxima execução. |
| Sim: Ajuste de percentil | O feedback de concessão de memória está sendo aplicado usando o algoritmo de concessão de percentil, que analisa mais histórico do que apenas a execução mais recente. |
| Sim: Estável | O feedback de concessão de memória foi aplicado e a memória concedida agora é estável, o que significa que o que foi concedido pela última vez para a execução anterior é o que foi concedido para a execução atual. |
O percentil e a memória do modo de persistência concedem feedback
Aplica-se a: SQL Server (a partir do SQL Server 2022 (16.x)), Banco de Dados SQL do Azure, Instância Gerenciada do SQL do Azure
Esse recurso foi introduzido no SQL Server 2022 (16.x), no entanto, esse aprimoramento de desempenho está disponível para consultas que operam no nível de compatibilidade de banco de dados 140 (introduzido no SQL Server 2017) ou superior, ou na dica de 140 e superior, e quando o QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n Repositório de Consultas está habilitado para o banco de dados e está em um estado de "leitura de gravação".
- O feedback de concessão de memória percentil é ativado por padrão no SQL Server 2022 (16.x), mas não tem efeito se o Repositório de Consultas não estiver ativado ou quando o Repositório de Consultas não estiver em um estado de "leitura/gravação".
- A persistência para concessão de memória, CE e comentários DOP está ativada 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á em um estado de "leitura de gravação".
- A persistência e o percentil para feedback de concessão de memória estão disponíveis no Banco de Dados SQL do Azure e são habilitados por padrão em todos os bancos de dados, tanto ativos como novos.
- Percentil e persistência para o feedback de concessão de memória não estão atualmente disponíveis na Instância do SQL Gerida pelo Azure.
É recomendável que você tenha uma linha de base de desempenho para sua carga de trabalho antes que o recurso seja habilitado para seu banco de dados. Os números da linha de base ajudarão você a determinar se você está obtendo o benefício pretendido do recurso.
O feedback de concessão de memória (MGF) é um recurso existente que ajusta o tamanho da memória alocada para uma consulta com base no desempenho anterior. No entanto, as fases iniciais deste projeto apenas armazenaram o ajuste de concessão de memória com o plano no cache – se um plano for removido do cache, o processo de feedback deve começar novamente, resultando em baixo desempenho nas primeiras vezes que uma consulta é 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 perdurem nas remoções de cache. A persistência e o percentil do feedback de concessão de memória abordam as limitações existentes do feedback de concessão de memória de maneira não intrusiva.
Além disso, os ajustamentos da dimensão da subvenção apenas tiveram em conta a subvenção utilizada mais recentemente. Portanto, se uma consulta parametrizada ou carga de trabalho exigir tamanhos de concessão de memória significativamente variáveis a cada execução, as informações de concessão mais recentes podem ser imprecisas. Pode estar fora de sintonia com as necessidades reais da consulta que está sendo executada. O feedback de concessão de memória neste cenário não é útil para o 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 feedback de concessão de memória sem percentil e modo 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 concedidas resulta em memória desperdiçada e insuficiente se a própria execução da consulta se alternar em termos da quantidade de memória. Nesse cenário, o feedback de concessão de memória desativa-se, reconhecendo que está causando mais prejuízo do que benefício.
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 vazamentos. Por exemplo, a mesma carga de trabalho alternada veria o seguinte comportamento de concessão de memória:
O otimizador de consultas utiliza um percentil elevado dos requisitos passados de dimensionamento de concessões de memória para as execuções do plano em cache, a fim de calcular os tamanhos das concessões de memória, usando dados persistentes no Query Store. O ajuste de percentil, que executará os ajustes de concessão de memória, é baseado no histórico recente de execuções. Com o tempo, a atribuição de memória reduz extravasamentos e desperdício de memória.
A persistência também se aplica ao feedback DOP e ao feedback CE.
Ativar e desativar recursos de feedback de concessão de memória
Desativar o feedback de concessão de memória no modo de linha sem alterar o nível de compatibilidade
O feedback de concessão de memória no modo por linha pode ser desativado no escopo do banco de dados ou da instrução, mantendo ainda o nível de compatibilidade do banco de dados 150 e superior. Para desabilitar a memória do modo de linha conceder feedback 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 reativar o feedback de concessão de memória em modo de linha para todas as execuções de consulta provenientes do banco de dados, execute o seguinte comando no contexto do banco de dados aplicável.
ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;
Você também pode desativar a memória do modo de linha conceder feedback 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 USE HINT tem precedência sobre uma configuração de escopo de banco de dados ou definição de sinalizador de rastreamento.
Habilite a persistência e o percentil de feedback de concessão de memória
A persistência e o feedback de percentil são habilitados por padrão no Banco de Dados SQL do Azure e no SQL Server 2022 (16.x).
Use o nível de compatibilidade de banco de dados 140 ou superior para o banco de dados ao qual você está conectado ao executar a consulta. Você pode alterar isso via ALTER DATABASE:
ALTER DATABASE <DATABASE NAME> SET COMPATIBILITY LEVEL = 140; -- OR HIGHER
O Repositório de Consultas deve ser habilitado para todos os bancos de dados em que a parte de persistência desse recurso é usada.
Desativar percentil
Para desabilitar o percentil de feedback de concessão de memória para todas as execuções de consulta originadas do banco de dados, execute o seguinte no contexto do banco de dados aplicável:
ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;
A configuração padrão para MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT é ON.
Desativar persistência
Para desabilitar a memória, conceda persistência de feedback para todas as execuções de consulta originadas do 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 desativação da persistência de feedback de concessão de memória também removerá os comentários coletados existentes.
A configuração padrão para MEMORY_GRANT_FEEDBACK_PERSISTENCE é ON.
Considerações para feedback de atribuição de memória
Você pode visualizar suas configurações atuais consultando sys.database_scoped_configurations.
Observação
Este recurso não funcionará se ambos BATCH_MODE_MEMORY_GRANT_FEEDBACK e ROW_MODE_MEMORY_GRANT_FEEDBACK estiverem definidos como OFF.
Dado que os dados de feedback agora persistem no Repositório de Consultas, há algum aumento nos requisitos de uso do Repositório de Consultas.
A concessão de memória baseada em percentil tende a reduzir derramamentos. Como não se baseia mais apenas na última execução, mas em uma observação das várias execuções passadas, isso pode aumentar o uso de memória para cargas de trabalho oscilantes com grande variação nos requisitos de concessão de memória entre as execuções.
A partir do SQL Server 2022 (16.x), quando o Repositório de Consultas para réplicas secundárias está ativado, o feedback da concessão de memória adapta-se às réplicas secundárias em grupos de disponibilidade. O feedback de concessão de memória pode aplicar o feedback de forma diferente em uma réplica primária e em uma réplica secundária. No entanto, 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. Qualquer feedback aplicado à réplica secundária quando ela se torna a réplica primária é perdido. A Loja de Consultas está disponível em réplicas do grupo de disponibilidade secundária a partir do SQL Server 2025 (17.x). Para obter mais informações, consulte Query Store para réplicas secundárias.