Feedback da CE (estimativa de cardinalidade)

Aplica-se a: SQL Server 2022 (16.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada do Azure SQL

A partir do SQL Server 2022 (16.x), o feedback da CE (estimativa de cardinalidade) faz parte da família de recursos de processamento de consultas inteligentes e aborda planos de execução de consulta abaixo do ideal para consultas repetidas quando esses problemas resultam de suposições incorretas do modelo de 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 único conjunto de modelos e pressupostos de CE que consiga acomodar a vasta variedade de cargas de trabalho dos clientes e distribuições de dados, o feedback da CE oferece uma solução adaptável com base nas características de tempo de execução da consulta. O feedback de CE identifica e usa uma suposição de modelo mais adequada a uma determinada consulta e distribuição de dados para aprimorar a qualidade do plano de execução da consulta. Atualmente, o feedback da CE pode identificar operadores de plano onde o número estimado de linhas e o número real de linhas difere muito. O feedback é aplicado quando ocorrem erros significativos de estimação do modelo e há um modelo alternativo viável para tentar.

Para outros recursos de feedback de consulta, consulte Feedback de concessão de memória e Feedback de grau de paralelismo (DOP).

Entenda o feedback da estimativa de cardinalidade (CE)

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 do feedback de estimativa de cardinalidade (CE)

O feedback da CE (estimativa de cardinalidade) aprende quais suposições de modelo de CE são ideais ao longo do tempo e, em seguida, aplica a suposição historicamente mais correta:

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 execução subsequente da mesma consulta é testada com um plano de consulta que ajusta a suposição impactante do modelo de CE e verifica se isso ajuda. Identificamos a incorreção comparando as linhas reais e as estimadas dos operadores do plano. Nem todos os erros podem ser corrigidos por variantes de modelo disponíveis no feedback da CE.

3. Caso melhore 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 que ajusta o modelo de estimativa, implementado através do mecanismo de dicas do Repositório de Consultas.

Somente o feedback verificado é armazenado. 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 feedback da estimativa de cardinalidade

O feedback da estimativa de cardinalidade (CE) aborda problemas de regressão detectados resultantes de pressupostos incorretos do modelo de CE ao usar a CE padrão (CE120 ou superior) e pode usar seletivamente pressupostos de modelo diferentes. Os cenários incluem correlação, contenção de associação e meta de linhas do otimizador.

Correlação de feedback da estimativa de cardinalidade (CE)

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 correlacionado (padrão para CE120 e superior), em que a cardinalidade é calculada usando uma variação do recuo exponencial, ordenando as seletividades do predicado mais seletivo ao 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, o feedback da CE tenta 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.

Para obter mais informações, consulte as versões da CE.

Contenção de junção do feedback da estimativa de cardinalidade

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:

• Contenção simples (padrão para CE70) pressupõe que os predicados de junção sejam totalmente correlacionados, na qual a seletividade do filtro é calculada primeiro e, em seguida, a seletividade da junção é levada em conta.

• Confinamento de base (padrão para CE120 e superior) não pressupõe nenhuma 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 é levada em consideração.

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.

Feedback da CE (estimativa de cardinalidade) e a meta de linha do otimizador de consulta

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 no runtime usando as palavras-chave TOP, IN 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 o objetivo 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 de maneira uniforme, mais páginas poderão ser examinadas do que o estimado, o que significa que o objetivo de linha se torna 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á atributos específicos para o feedback da CE, mas há um atributo listado para a dica do Repositório de Consultas. Procure o QueryStoreStatementHintSource como CE feedback.

Considerações sobre o feedback sobre a estimativa de cardinalidade (CE)

• Para habilitar o feedback da estimativa de cardinalidade (CE), habilite o nível de compatibilidade 160 para o banco de dados ao qual você está conectado ao executar a consulta. O Repositório de Consultas precisa estar habilitado e no modo READ_WRITE para cada banco de dados em que o feedback da CE é usado.

• Para desabilitar o feedback de CE no nível do banco de dados, use a CE_FEEDBACKconfiguração com escopo de banco de dados. Por exemplo, no banco de dados de usuário:

  ALTER DATABASE SCOPED CONFIGURATION
  SET CE_FEEDBACK = OFF;
  

• Para desativar o feedback da CE no nível de consulta, use a dica de consulta DISABLE_CE_FEEDBACK.

A atividade de feedback da CE pode ser visualizada por meio dos 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 feedback podem ser acompanhadas usando a exibição de catálogo sys.query_store_plan_feedback.

Se uma consulta tiver um plano de consulta forçado por meio do Repositório de Consultas, o feedback de CE não será usado 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, consulte dicas de consulta e dicas do Repositório de Consultas.

A partir do SQL Server 2022 (16.x), quando o Repositório de Consultas para réplicas secundárias está habilitado, o feedback de CE não reconhece réplicas secundárias em grupos de disponibilidade. Atualmente, o feedback da CE beneficia apenas as réplicas primárias. Em caso de failover, o feedback aplicado às réplicas primárias ou secundárias é perdido. O Repositório de Consultas está disponível em réplicas de grupo de disponibilidade secundárias a partir do SQL Server 2025 (17.x). Para obter mais informações, consulte Repositório de Consultas para secundários legíveis.

Persistência do feedback de estimativa de cardinalidade (CE)

Aplicável a: SQL Server 2022 (16.x) e versões posteriores, Banco de Dados SQL do Azure e Instância Gerenciada de SQL do Azure.

O feedback de estimativa de cardinalidade (CE) pode detectar cenários em que a otimização de objetivo de linha deve ser persistida e manter essa alteração persistindo-a no Repositório de Consultas na forma de uma dica do Repositório de Consultas. A nova otimização é usada para execuções futuras da consulta. O feedback de CE persiste em outros cenários além dos padrões de consulta de otimização de meta de linha, conforme detalhado em cenários de feedback. Atualmente, o feedback da CE lida com cenários de seletividade de predicados usados pelo modelo de correlação da CE e com cenários de predicados de junção tratados pelo modelo de contenção da CE.

Esse recurso foi introduzido no SQL Server 2022 (16.x); no entanto, esse aprimoramento de desempenho está disponível para consultas executadas no nível de compatibilidade 160 ou superior do banco de dados, ou para as quais é usada a dica QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n de 160 ou superior, e quando o Repositório de Consultas está habilitado para o banco de dados e está no estado de “leitura e gravação”.

Problemas conhecidos com o feedback de estimativa de cardinalidade (CE)

Detalhes dos problemas conhecidos

Desempenho lento do SQL Server depois de aplicar a Atualização Cumulativa 8 para SQL Server 2022 sob determinadas condições

Com a Atualização Cumulativa 8 para SQL Server 2022 (16.x) e versões posteriores, o SQL Server pode apresentar aumentos inesperados na utilização da CPU e da memória. Além disso, um aumento nas esperas do tipo RESOURCE_SEMAPHORE_QUERY_COMPILE também pode ser observado. Você também pode notar aumentos constantes no número de objetos do Cache de Planos em uso, que se aproximam dos limites do Cache de Planos, e que limpar manualmente o Cache de Planos com técnicas como ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, DBCC FREESYSTEMCACHE ou DBCC FREEPROCCACHE não ajuda. Esse comportamento só foi observado por alguns clientes.

Esse problema não afeta todas as cargas de trabalho e depende do número de planos diferentes que foram gerados, bem como do número de planos elegíveis para o recurso de feedback da CE ser ativado. Embora o feedback do CE esteja analisando os operadores do plano em busca de estimativas incorretas significativas do modelo, há um cenário em que um plano referenciado pode ser desreferenciado durante essa fase de análise. Essa situação impede que o plano seja removido da memória usando o algoritmo usual de Least Recently Used (LRU). O mecanismo LRU é uma das formas pelas quais o SQL Server aplica políticas de descarte de planos. O SQL Server também remove planos da memória se o sistema estiver sob pressão de memória. Quando o SQL Server tenta remover os planos que foram desreferenciados incorretamente, não é possível remover esses planos do cache de planos, o que faz com que o cache continue a aumentar. O cache crescente pode começar a causar compilações adicionais que, em última análise, usa mais CPU e memória. Para obter mais informações, consulte Aspectos internos do cache de planos.

Indício: o número de entradas em uso do cache de planos e que estão marcadas como sujas dos Planos SQL ou dos Planos de Objetos aumenta com o tempo para 50 mil ou mais. Se você observar entradas no cache de planos que começam a se aproximar desse nível, juntamente com aumentos inesperados no uso da CPU, seu sistema pode estar enfrentando esse problema. Uma correção é fornecida na Atualização Cumulativa 12 do SQL Server 2022 (16.x). Consulte KB5033663.

Para monitorar o número de entradas do cache de planos que o sistema está usando, os exemplos a seguir podem ser usados​como uma exibição pontual do número de entradas do cache de planos existentes. Por exemplo, monitorar periodicamente o número de entradas do cache de planos marcadas como sujas ao longo do tempo é uma forma de monitorar esse fenômeno.

SELECT CASE WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
            WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
            ELSE '[All other cache stores]'
       END AS PlanType,
       COUNT(*) AS [Number of plans marked to be removed]
FROM sys.dm_os_memory_cache_entries AS mce
     LEFT OUTER JOIN sys.dm_exec_cached_plans AS ecp
         ON mce.memory_object_address = ecp.memory_object_address
WHERE mce.is_dirty = 1
      AND ecp.bucketid IS NULL
GROUP BY CASE WHEN mce.[name] LIKE 'SQL Plan%' THEN 'SQL Plans'
              WHEN mce.[name] LIKE 'Object Plan%' THEN 'Object Plans'
              ELSE '[All other cache stores]'
         END;

Outro conjunto de consultas que também fornece as mesmas informações do exemplo anterior, além de permitir que você observe métricas de desempenho adicionais. As taxas de acerto do cache de planos diminuem, assim como o número de compilações em relação ao número de solicitações em lote por segundo. As consultas a seguir podem ser usadas para monitorar seu sistema ao longo do tempo. Monitorar a Taxa de acertos no cache (quedas inesperadas), os Objetos de cache em uso (aumentos na contagem para níveis próximos de 50 mil sem apresentar redução) e uma taxa de Solicitações em lote por segundo menor do que a esperada, em comparação com o aumento em Compilações por segundo.

--SQL Plan (Adhoc and Prepared plans)
SELECT CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
            WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
            WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
            WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
       END AS [SQLServer:Plan Cache (SQL Plans)],
       CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
            ELSE FORMAT(cntr_value, '#,###')
       END AS [Counter Value],
       CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN
                 FORMAT(TRY_CONVERT (DECIMAL (5, 2), (cntr_value * 1.0 / NULLIF ((SELECT cntr_value
                     FROM sys.dm_os_performance_counters
                     WHERE [object_name] LIKE '%:Plan Cache%'
                           AND [counter_name] = 'Cache Hit Ratio Base'
                           AND instance_name LIKE 'SQL Plan%'), 0))), '0.00%')
       END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
      AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
      AND instance_name LIKE 'SQL Plan%'
ORDER BY [counter_name];

--Module/Stored procedure based plans
SELECT CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN 'Cache Hit Ratio'
            WHEN [counter_name] = 'Cache Object Counts' THEN 'Cache Object Counts'
            WHEN [counter_name] = 'Cache Objects in use' THEN 'Cache Objects in use'
            WHEN [counter_name] = 'Cache Pages' THEN 'Cache Pages'
       END AS [SQLServer:Plan Cache (Object Plans)],
       CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN NULL
            ELSE FORMAT(cntr_value, '#,###')
       END AS [Counter Value],
       CASE WHEN [counter_name] = 'Cache Hit Ratio' THEN
                 FORMAT(TRY_CONVERT (DECIMAL (5, 2), (cntr_value * 1.0 / NULLIF ((SELECT cntr_value
                     FROM sys.dm_os_performance_counters
                     WHERE [object_name] LIKE '%:Plan Cache%'
                           AND [counter_name] = 'Cache Hit Ratio Base'
                           AND instance_name LIKE 'Object Plan%'), 0))), '0.00%')
       END AS [SQL Plan Cache Hit Ratio]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:Plan Cache%'
      AND [counter_name] IN ('Cache Hit Ratio', 'Cache Object Counts', 'Cache Objects in use', 'Cache Pages')
      AND instance_name LIKE 'Object Plan%'
ORDER BY [counter_name];

SELECT CASE WHEN [counter_name] = 'Batch Requests/sec' THEN 'Batch Requests/sec'
            WHEN [counter_name] = 'SQL Compilations/sec' THEN 'SQL Compilations/sec'
       END AS [SQLServer:SQL Statistics],
       FORMAT(cntr_value, '#,###') AS [Counter Value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%:SQL Statistics%'
      AND counter_name IN ('Batch Requests/sec', 'SQL Compilations/sec');

Solução alternativa

Se o sistema continuar apresentando os sintomas descritos anteriormente após aplicar a Atualização Cumulativa 12 KB5033663, o recurso de comentários do CE poderá ser desativado no nível de banco de dados.

Para recuperar a memória do cache de planos ocupada por esse problema, é necessária uma reinicialização da instância do SQL Server. Essa ação de reinicialização poderá ser executada depois que o recurso de feedback da CE for desabilitado. Para desabilitar o feedback de CE no nível do banco de dados, use a CE_FEEDBACKconfiguração com escopo de banco de dados. Por exemplo, no banco de dados de usuário:

ALTER DATABASE SCOPED CONFIGURATION
SET CE_FEEDBACK = OFF;

Problemas de feedback e de relatório

Caso tenha algum feedback ou dúvidas, envie um email para CEFfeedback@microsoft.com

Conteúdo relacionado

• Feedback de Estimativa de Cardinalidade no SQL Server 2022
• Processamento inteligente de consultas em bancos de dados SQL
• Recursos de processamento inteligente de consultas em detalhes
• Estimativa de cardinalidade (SQL Server)
• RECONFIGURAR (Transact-SQL)
• Monitorar e ajustar o desempenho
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)