Recursos de feedback de processamento de consulta

Este artigo tem descrições detalhadas de vários recursos de comentários do IQP (processamento de consulta inteligente). Os recursos de comentários de processamento de consulta fazem parte da família de recursos de processamento de consulta inteligente. Os comentários de processamento de consulta são um processo pelo qual o processador de consultas em SQL Server, SQL do Azure Banco de Dados e Instância Gerenciada de SQL do Azure usa dados históricos sobre a execução de uma consulta para decidir se a consulta pode receber ajuda de uma ou mais alterações na maneira como ela é compilada e executada. Os dados de desempenho são coletados no repositório de consultas, com várias sugestões para melhorar a execução da consulta. Se tiver êxito, persistiremos essas modificações no disco na memória e/ou no repositório de consultas para uso futuro. Se as sugestões não produzirem melhorias suficientes, elas serão descartadas e a consulta continuará sendo executada sem esses comentários.

Os recursos de comentários discutidos neste artigo são:

• Comentários sobre concessão de memória
  • Modo de lote
  • Modo de linha
  • Percentil e modo de persistência
• Comentários de grau de paralelismo
• Comentários sobre estimativa de cardinalidade

Comentários de concessão de memória

Às vezes, uma consulta é executada com uma concessão de memória muito grande ou muito pequena. Se a concessão de memória for muito grande, inibiremos o paralelismo no servidor. Se for muito pequeno, poderemos despejar no disco, o que é uma operação cara. Os comentários de concessão de memória tentam lembrar as necessidades de memória de uma execução anterior (começando no SQL Server 2022 (16.x), várias execuções) de uma consulta e ajustar a concessão fornecida à consulta adequadamente. Esse recurso foi lançado em três ondas. Comentários de concessão de memória do modo de lote, seguidos por comentários de concessão de memória no modo de linha e em SQL Server 2022 (16.x), estamos introduzindo comentários de concessão de memória sobre persistência no disco usando o Repositório de Consultas e um algoritmo aprimorado conhecido como concessão de percentil.

Comentários de concessão de memória de modo de lote

Aplica-se a: SQL Server (No SQL Server 2017 (14.x) em diante), Banco de Dados SQL do Azure

O plano de execução de uma consulta inclui a memória mínima necessária para execução e o tamanho de concessão de memória ideal para que todas as linhas se ajustem 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. Planos com concessões de memória inferiores a 1 MB não serão recalculados para excedentes.

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

A concessão de memória ajustada aparece no plano real (pós-execução) por meio da GrantedMemory propriedade .

Você pode ver essa propriedade no operador raiz do plano de exibiçã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 suas cargas de trabalho sejam qualificadas automaticamente para essa melhoria, 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 comentários de concessão de memória é desabilitado 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 comentários de concessão de memória introduzidos no SQL Server 2022 (16.x). O recurso de persistência dos comentários de concessão de memória requer que o Repositório de Consultas seja habilitado no banco de dados e definido como modo de "gravação de leitura".

Para obter mais informações sobre detecção de parâmetros e confidencialidade de parâmetros, consulte o 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. No entanto, são as execuções consecutivas dessa instrução que se beneficiam dos ajustes de comentários 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 de SQL Server 2022 (16.x), as alterações não eram capturadas no Repositório de Consultas.

Os comentários não serão mantidos se o plano for removido do cache. Os comentários também serão perdidos 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 comentário 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 foi usada OPTION (RECOMPILE) foi armazenada em cache e executada novamente, as segundas e posteriores execuções consecutivas podem se beneficiar de comentários de concessão de memória.

Acompanhar a atividade de comentários de concessão de memória

Você pode acompanhar eventos de comentários de concessão de memória usando o memory_grant_updated_by_feedback evento estendido. 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 comentários de concessão de memória no modo de lote 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 os comentários de concessão de memória do modo de lote para todas as execuções de consulta originadas 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 em sys.database_scoped_configurations.

Para habilitar novamente os comentários de concessão de memória do 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;

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 com escopo de banco de dados ou configuração de sinalizador de rastreamento.

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

Aplica-se a: SQL Server (No SQL Server 2019 (15.x) em diante), Banco de Dados SQL do Azure

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 os comentários de concessão de memória do modo de linha 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 executar a consulta.

Exemplo:

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

Assim como acontece com os comentários de concessão de memória do modo de lote, a atividade de comentários de concessão de memória do modo de linha é visível por meio do memory_grant_updated_by_feedback XEvent. Também estamos introduzindo dois novos atributos de plano de execução de consulta para obter melhor visibilidade do estado atual de uma operação de comentários de concessão de memória para o modo de linha e lote.

Os comentários de concessão de memória não exigem o Repositório de Consultas, no entanto, as melhorias de persistência introduzidas no SQL Server 2022 (16.x) exigem que o Repositório de Consultas seja habilitado para o banco de dados e em um estado de "gravação de leitura". Para obter mais informações sobre persistência, consulte Comentários de concessão de memória do modo de persistência e percentil mais adiante neste artigo.

A atividade de comentários de concessão de memória do modo de linha é visível por meio do memory_grant_updated_by_feedback evento estendido.

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

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

Os valores apresentados nesse atributo são os seguintes:

IsMemoryGrantFeedbackAdjusted Valor	Descrição
Não: Primeira execução	Os comentários de concessão de memória não ajustam a memória para a primeira compilação e a execução associada.
Não: Concessão precisa	Se não houver despejo no disco e a instrução usar pelo menos 50% da memória concedida, os comentários de concessão de memória não serão disparados.
Não: Comentários desabilitados	Se os comentários de concessão de memória forem disparados continuamente e flutuarem entre as operações de aumento de memória e redução de memória, o mecanismo de banco de dados desabilitará os comentários de concessão de memória para a instrução .
Sim: Ajuste	Os comentários de concessão de memória foram aplicados e podem ser ainda mais ajustados para a próxima execução.
Sim: Estável	Os comentários de concessão de memória foram aplicados e a memória concedida está estável, ou seja, o que foi concedido para a execução anterior é o mesmo que foi concedido para a execução atual.

Desabilitar comentários de concessão de memória do modo de linha 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 os comentários de concessão de memória do modo de linha 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:

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 com escopo de banco de dados ou configuração de sinalizador de rastreamento.

Comentários de concessão de memória no modo de percentil e persistência

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores

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 do banco de dados 140 (introduzido no SQL Server 2017) ou superior, ou a dica QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n de 140 e superior, e quando Repositório de Consultas está habilitado para o banco de dados e está em um estado de "gravação de leitura".

• Os comentários de concessão de memória percentil são habilitados por padrão no SQL Server 2022 (16.x), mas não terá efeito se Repositório de Consultas não estiver habilitado e em um estado de "gravação de leitura".
• A persistência para concessão de memória, CE e comentários do DOP está ativada por padrão no SQL Server 2022 (16.x), mas não terá efeito se Repositório de Consultas não estiver habilitado e em um estado de "gravação de leitura".
• No momento, os comentários de concessão de memória percentil não estão disponíveis no banco de dados SQL do Azure e Instância Gerenciada de SQL do Azure.
• No momento, a persistência não está disponível no Banco de Dados SQL do Azure e Instância Gerenciada de SQL do 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 de linha de base ajudarão você a determinar se você está recebendo o benefício pretendido do recurso.

O MGF (comentários de concessão de memória) é um recurso existente que ajusta o tamanho da memória alocada para uma consulta com base no desempenho passado. 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 é persistir as informações de concessão com as outras informações de consulta no Repositório de Consultas para que os benefícios durem 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 uma consulta ou carga de trabalho parametrizada exigir tamanhos de concessão de memória significativamente variados com cada execução, as informações de concessão mais recentes poderão ser imprecisas. Ele pode estar fora de sintonia com as necessidades reais da consulta que está sendo executada. Os comentários de concessão de memória neste cenário não são úteis para o desempenho, pois 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 comentários de concessão de memória sem percentil e modo de persistência.

Como você pode ver, neste comportamento de consulta incomum, mas possível, a oscilação entre os valores de memória reais necessários e concedidos resultará em memória desperdiçada e insuficiente se a execução da consulta em si se alternar em termos da quantidade de memória. Nesse cenário, os comentários de concessão de memória se desabilitam, reconhecendo que ele está fazendo mais mal do que bem.

Usando um cálculo baseado em percentil no 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 verá o seguinte comportamento de concessão de memória:

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

A persistência também se aplica aos comentários do DOP e aos comentários do CE, também detalhados neste artigo.

Habilitar comentários de concessão de memória: persistência e percentil

Para habilitar a persistência e o percentil de comentários de concessão de memória, use o nível de compatibilidade do banco de dados 140 ou superior para o banco de dados ao executar a consulta. Os comentários de persistência e percentil são habilitados por padrão.

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 de comentários 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 = OFF;

A configuração padrão de MEMORY_GRANT_FEEDBACK_PERCENTILE é OFF.

Desabilitar 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 sobre comentários sobre concessão de memória

Você pode ver as configurações atuais consultando sys.database_scoped_configurations.

Observação

Que esse 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 comentários agora persistem no Repositório de Consultas, há algum 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 Repositório de Consultas para réplicas secundárias está habilitada, os comentários de concessão de memória são réplica para réplicas secundárias em grupos de disponibilidade. Os comentários de concessão de memória podem aplicar comentários de forma diferente em um réplica primário e em um réplica secundário. No entanto, os comentários de concessão de memória não são persistentes em réplicas secundárias e, no failover, os comentários de concessão de memória do réplica primário antigo são aplicados à nova réplica primária. Todos os comentários aplicados ao réplica secundário quando ele se torna o réplica primário são perdidos. Para obter mais informações, consulte Repositório de Consultas para réplicas secundárias.

Comentários de DOP (grau de paralelismo)

Aplica-se a: SQL Server 2022 (16.x) e posterior, Instância Gerenciada de SQL do Azure, banco de dados SQL do Azure (versão prévia)

SQL Server 2022 (16.x) introduziu um novo recurso chamado comentários de grau de paralelismo (DOP) para melhorar o desempenho da consulta identificando ineficiências de paralelismo para consultas repetidas, com base no tempo decorrido e nas esperas. Os comentários do DOP fazem parte da família de recursos de processamento de consulta inteligente e resolvem o uso não ideal do paralelismo para consultas repetidas. Esse cenário ajuda a otimizar o uso de recursos e aprimorar a escalabilidade das cargas de trabalho, quando o paralelismo excessivo pode causar problemas de desempenho. Em vez de incorrer nas dores de um padrão abrangente ou ajustes manuais em cada consulta, os comentários do DOP autoajustam o DOP para evitar os problemas descritos acima.

Observação

Atualmente, esse recurso está disponível no Banco de Dados SQL do Azure como uma versão prévia limitada. Para obter mais informações e ver como se candidatar à versão prévia, confira Comunicado dos Comentários do DOP Versão prévia limitado.

Em vez de incorrer nas dores de um padrão abrangente ou ajustes manuais em cada consulta, os comentários do DOP autoajustam o DOP para evitar o excesso de paralelismo. Se o uso de paralelismo for considerado ineficiente, os comentários do DOP reduzirão o DOP para a próxima execução da consulta, de qualquer que seja o DOP configurado, e verifique se ele ajuda.

O paralelismo geralmente é benéfico para relatórios e consultas analíticas ou consultas que, de outra forma, lidam com grandes quantidades de dados. Por outro lado, as consultas centradas em OLTP executadas em paralelo podem enfrentar problemas de desempenho quando o tempo gasto coordenando todos os threads supera as vantagens de usar um plano paralelo. Para obter mais informações, confira execução em plano paralelo.

• Para habilitar comentários do DOP, habilite a configuração no escopo do DOP_FEEDBACK banco de dados em um banco de dados.

• O Repositório de Consultas precisa ser habilitado para cada banco de dados em que os comentários do DOP são usados e no estado "Gravação de leitura". Os comentários serão mantidos na exibição de catálogo sys.query_store_plan_feedback quando atingirmos um grau estável de valor de comentários de paralelismo.

• Os comentários do DOP estão disponíveis para consultas que operam no nível de compatibilidade do banco de dados 160 (introduzido com SQL Server 2022 (16.x)) ou superior.

• Somente os comentários verificados são persistentes. Se o DOP ajustado resultar em uma regressão de desempenho, os comentários do DOP voltarão para o último DOP conhecido. Nesse contexto, uma consulta cancelada pelo usuário também é percebida como uma regressão. Os comentários do DOP não recompilam os planos.

• Os comentários estáveis são reverenciados na recompilação do plano e podem ser reajustadas para cima ou para baixo, mas nunca acima da configuração MAXDOP (incluindo uma dica MAXDOP).

• Para desabilitar os comentários do DOP no nível do banco de dados, use a configuração no escopo do ALTER DATABASE SCOPED CONFIGURATION SET DOP_FEEDBACK = OFF banco de dados.

• Para desabilitar os comentários do DOP no nível da consulta, use a dica de consulta DISABLE_DOP_FEEDBACK.

• A partir do SQL Server 2022 (16.x), quando Repositório de Consultas para réplicas secundárias está habilitada, os comentários do DOP também são réplica para réplicas secundárias em grupos de disponibilidade. Os comentários do DOP podem aplicar comentários de forma diferente em um réplica primário e em um réplica secundário. No entanto, os comentários do DOP não são persistentes em réplicas secundárias e, no failover, os comentários do DOP do réplica primário antigo não são aplicados à nova réplica primária. No failover, os comentários aplicados às réplicas primárias ou secundárias são perdidos. Para obter mais informações, consulte Repositório de Consultas para réplicas secundárias.

Implementação de comentários do DOP

Os comentários do DOP identificarão ineficiências de paralelismo para consultas repetidas, com base no tempo decorrido e nas esperas. Se o uso de paralelismo for considerado ineficiente, os comentários do DOP reduzirão o DOP para a próxima execução da consulta, de qualquer que seja o DOP configurado, e verificarão se ele ajuda.

Para avaliar a elegibilidade da consulta, o tempo decorrido da consulta ajustado é medido em algumas execuções. O tempo decorrido total para cada consulta é ajustado ignorando as esperas de E/S do Buffer, Trava do Buffer e de E/S de Rede externas à execução de consulta paralela. O objetivo do recurso de comentários do DOP é aumentar a simultaneidade geral e reduzir significativamente as esperas, mesmo que isso aumente ligeiramente o tempo decorrido da consulta.

Somente os comentários verificados são persistentes. Se o DOP ajustado resultar em uma regressão de desempenho, os comentários do DOP voltarão para o último DOP conhecido. Nesse contexto, uma consulta cancelada pelo usuário também é percebida como uma regressão.

Observação

Os comentários do DOP não recompilam os planos.

Considerações sobre comentários do DOP

O DOP mínimo para qualquer consulta ajustada com comentários do DOP é 2. As execuções em série estão fora do escopo para comentários do DOP.

As informações de comentários podem ser rastreadas usando a exibição de catálogo sys.query_store_plan_feedback .

Se uma consulta tiver um plano de consulta forçado por meio de Repositório de Consultas, os comentários do DOP ainda podem ser usados para essa consulta.

Se uma consulta usar a dica MAXDOP, como dicas de consulta embutidas em código ou por meio do mecanismo de dicas Repositório de Consultas e a dica MAXDOP for maior que 2, os comentários do DOP reduzirão o DOP usando o valor sugerido como o teto. Para obter mais informações, confira Dicas (Transact-SQL) – Consulta e dicas do Repositório de Consultas.

Eventos estendidos para comentários do DOP

Os seguintes XEs estão disponíveis para o recurso:

• dop_feedback_eligible_query: ocorre quando o plano de consulta se torna qualificado para comentários do DOP. Eventos adicionais poderão ser acionados se ocorrer uma recompilação ou uma reinicialização da instância do SQL Server.
• dop_feedback_provided: ocorre quando um comentário do DOP forneceu dados para uma determinada consulta. Esse evento contém estatísticas de linha de base quando os comentários são fornecidos pela primeira vez e estatísticas de comentários anteriores quando comentários subsequentes são fornecidos.
• dop_feedback_validation: ocorre quando a validação acontece para as estatísticas de runtime de consulta em relação a uma linha de base ou as estatísticas de comentários anteriores.
• dop_feedback_stabilized: ocorre quando os comentários do DOP são estabilizados para uma consulta.
• dop_feedback_reverted: ocorre quando um comentário do DOP é revertido. O evento será acionado quando a validação de comentários falhar nos primeiros comentários fornecidos. O sistema será revertido para nenhum estado de comentários.
• dop_feedback_analysis_stopped : ocorre quando a análise de comentários do DOP é interrompida para uma consulta.

Comentários de CE (estimativa de cardinalidade)

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores.

A partir do SQL Server 2022 (16.x)), os comentários da CE (Estimativa de Cardinalidade) fazem parte da família de recursos de processamento de consulta inteligente e aborda planos de execução de consulta abaixo do ideal para consultas repetidas quando esses problemas resultam de suposições incorretas do modelo ce. Esse cenário ajuda a reduzir os riscos de regressão relacionados à CE padrão ao atualizar versões mais antigas do Mecanismo de Banco de Dados.

Como não há um só conjunto de modelos e suposições de CE que acomode a ampla matriz de cargas de trabalho do cliente e distribuições de dados, os comentários da CE são uma solução adaptável com base nas características do runtime de consulta. Os comentários da CE identificarão e usarão uma suposição de modelo mais adequada a uma determinada distribuição de consulta e de dados para aprimorar a qualidade do plano de execução de consulta. Atualmente, os comentários da CE podem identificar operadores de plano em que o número estimado de linhas e o número real de linhas são muito diferentes. Os comentários são aplicados quando ocorrem erros significativos de estimativa de modelo e há um modelo alternativo viável para experimentar.

• A partir do SQL Server 2022 (16.x), quando Repositório de Consultas para réplicas secundárias estiver habilitada, os comentários da CE não estarão réplica cientes para réplicas secundárias em grupos de disponibilidade. Atualmente, os comentários da CE beneficiam apenas as réplicas primárias. Para obter mais informações, consulte Repositório de Consultas para réplicas secundárias.

Entender a estimativa de cardinalidade

A CE (estimativa de cardinalidade) é como o otimizador de consulta pode estimar o número total de linhas processadas em cada nível de um plano de consulta. A estimativa de cardinalidade no SQL Server é derivada principalmente de histogramas criados quando índices ou estatísticas são criados, seja manual ou automaticamente. Às vezes, o SQL Server também usa informações de restrição e novas consultas lógicas para determinar a cardinalidade.

Diferentes versões do Mecanismo de Banco de Dados usam diferentes suposições de modelo de CE considerando como os dados são distribuídos e consultados. Confira as versões da CE para obter mais informações.

Implementação de comentários da CE

Os comentários da CE aprendem quais suposições de modelo de CE são ideais ao longo do tempo e depois aplicam a suposição mais correta de acordo com o histórico:

1. Os comentários da CE identificam suposições relacionadas ao modelo e avaliam se elas são precisas para consultas repetidas.

2. Se uma suposição parecer incorreta, uma próxima execução da mesma consulta será testada com um plano de consulta que ajuste a suposição do modelo de CE impactante e verifique se ela ajuda. Identificamos a inserção examinando linhas reais versus estimadas dos operadores de plano. Nem todos os erros podem ser corrigidos por variantes de modelo disponíveis nos comentários da CE.

3. Se ela aprimorar a qualidade do plano, o plano de consulta antigo será substituído por um plano de consulta que usa a dica de consulta USE HINT apropriada ajustando o modelo de estimativa, implementado por meio do mecanismo de dica do Repositório de Consultas.

Somente os comentários verificados são persistentes. Os comentários de CE não serão usados para essa consulta se a suposição do modelo ajustado resultar em uma regressão de desempenho. Nesse contexto, uma consulta cancelada pelo usuário também é percebida como uma regressão.

Cenários de comentários da CE

Os comentários da CE abordam problemas de regressão percebidos resultantes de suposições incorretas do modelo de CE ao usar a CE padrão (CE120 ou superior) e podem usar seletivamente diferentes suposições de modelo. Os cenários incluem Correlação, Independência de Junção e Meta de linha do Otimizador.

Correlation

Quando o Otimizador de Consulta estima a seletividade de predicados em uma determinada tabela ou exibição, ou o número de linhas que satisfazem o determinado predicado, ele usa suposições de modelo de correlação. Essas suposições podem ser que os predicados são:

• Totalmente independentes (padrão para CE70), em que a cardinalidade é calculada multiplicando as seletividades de todos os predicados.

• Parcialmente correlacionados (padrão para CE120 e superior), em que a cardinalidade é calculada usando uma variação na retirada exponencial, ordenando as seletividades do predicado mais seletivo para o menos seletivo.

• Totalmente correlacionados, em que a cardinalidade é calculada usando as seletividades mínimas para todos os predicados.

O exemplo a seguir usa a correlação parcial quando a compatibilidade do banco de dados é definida como 120 ou superior:

USE AdventureWorks2016_EXT;
GO
SELECT AddressID, AddressLine1, AddressLine2
FROM Person.Address
WHERE StateProvinceID = 79 AND City = N'Redmond';
GO

Quando a compatibilidade do banco de dados for definida como 160 e a correlação padrão for usada, os comentários da CE tentarão direcionar corretamente a correlação uma etapa de cada vez considerando se a cardinalidade estimada foi subestimada ou superestimada em comparação com o número real de linhas. Use a correlação completa se um número real de linhas for maior que a cardinalidade estimada. Use a independência completa se um número real de linhas for menor que a cardinalidade estimada.

Confira as versões da CE para obter mais informações.

Confinamento de junção

Quando o Otimizador de Consulta estima a seletividade de predicados de junção e de predicados de filtro aplicáveis, ele usa suposições de modelo de confinamento. As suposições são:

• Confinamento simples (padrão para CE70) pressupõe que os predicados de junção estejam totalmente correlacionados, em que a seletividade do filtro é calculada primeiro e depois a seletividade de junção é fatorada.

• Confinamento base (padrão para CE120 e superior) pressupõe que não haja correlação entre predicados de junção e filtros downstream,

em que a seletividade de junção é calculada primeiro e depois a seletividade do filtro é fatorada.

O exemplo a seguir usa o confinamento base quando a compatibilidade do banco de dados é definida como 120 ou superior:

USE AdventureWorksDW2016_EXT;
GO
SELECT *
FROM dbo.FactCurrencyRate AS f
INNER JOIN dbo.DimDate AS d ON f.DateKey = d.DateKey
WHERE d.MonthNumberOfYear = 7 AND f.CurrencyKey = 3 AND f.AverageRate > 1;
GO

Confira as versões da CE para obter mais informações.

Meta de linha do otimizador

Quando o Otimizador de Consulta estima a cardinalidade de um plano de execução, ele geralmente pressupõe que todas as linhas qualificadas de todas as tabelas devem ser processadas. No entanto, alguns padrões de consulta fazem com que o Otimizador de Consulta procure um plano que retornará um número menor de linhas para reduzir a E/S. Se a consulta especificar um número de destino de linhas (meta de linha) que possa ser esperado em tempo de execução usando as palavras-chave TOPIN ou EXISTS, a dica de consulta FAST ou uma instrução SET ROWCOUNT, essa meta de linha será usada durante o processo de otimização de consulta, como no seguinte exemplo:

USE AdventureWorks2016_EXT;
GO
SELECT TOP 1 soh.*
FROM Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID;
GO

Quando o plano de meta de linha é aplicado, o número estimado de linhas no plano de consulta é reduzido porque o Otimizador de Consulta pressupõe que um número menor de linhas precisará ser processado para atingir a meta de linha.

Embora a meta de linha seja uma estratégia de otimização benéfica para determinados padrões de consulta, se os dados não forem distribuídos uniformemente, mais páginas poderão ser examinadas depois estimadas, o que significa que a meta de linha será ineficiente. Os comentários da CE podem desabilitar ao exame de meta de linha e habilitar uma busca quando essa ineficiência for detectada.

No plano de execução, não há nenhum atributo específico para comentários da CE, mas haverá um atributo listado para a dica de Repositório de Consultas. Procure o QueryStoreStatementHintSource para ser CE feedback.

Considerações para comentários da CE

Para habilitar os comentários da CE, habilite o nível de compatibilidade do banco de dados 160 para o banco de dados conectado durante a execução da consulta. O Repositório de Consultas deve ser habilitado e no modo READ_WRITE para cada banco de dados em que os comentários da CE são usados.

A atividade de comentários da CE é visível por meio de XEvents query_feedback_analysis e query_feedback_validation.

As dicas definidas pelos comentários da CE podem ser acompanhadas usando a exibição de catálogo sys.query_store_query_hints.

As informações de comentários podem ser rastreadas usando a exibição de catálogo sys.query_store_plan_feedback .

Para desabilitar os comentários da CE no nível do banco de dados, use a configuração no escopo do ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF banco de dados.

Para desabilitar os comentários da CE no nível da consulta, use a dica de consulta DISABLE_CE_FEEDBACK.

Se uma consulta tiver um plano de consulta forçado por meio de Repositório de Consultas, os comentários da CE não serão usados para essa consulta.

Se uma consulta usa dicas de consulta embutidas em código ou dicas do Repositório de Consultas definidas pelo usuário, os comentários da CE não são usados nessa consulta. Para obter mais informações, confira Dicas (Transact-SQL) – Consulta e Dica do Repositório de Consultas.

Comentários e relatórios de problemas

Para comentários ou perguntas, envie um email CEFfeedback@microsoft.com

Próximas etapas

• Processamento de consulta inteligente
• Detalhes de processamento de consulta inteligente
• Configurar a opção de configuração de servidor max degree of parallelism
• Estimativas de cardinalidade
• RECONFIGURE (Transact-SQL)
• Monitorar e ajustar o desempenho
• Configurar operações paralelas de índice
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)