Feedback da CE (estimativa de cardinalidade)

  • Artigo

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

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 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, 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. Aplica-se o feedback quando ocorrem erros significativos de estimação do modelo e há um modelo alternativo viável para tentar.

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

Entenda o feedback da CE (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 feedback da CE (estimativa de cardinalidade)

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

O feedback da CE (estimativa de cardinalidade) aborda problemas de regressão percebidos resultantes de suposições incorretas de modelo de CE ao usar a CE padrão (CE120 ou superior) e pode usar seletivamente diferentes suposições de modelo. Os cenários CE incluem correlação, contenção de junção e meta de otimizador de linha.

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

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, 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.

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

Confinamento de junção de feedback da CE (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:

  • 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 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 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á atributos específicos para o feedback da CE, mas haverá um atributo listado para a dica do Repositório de Consultas. Procure o QueryStoreStatementHintSource como CE feedback.

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

  • Para habilitar o feedback da CE (estimativa de cardinalidade), 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 precisa estar habilitado e no modo READ_WRITE para cada banco de dados em que o feedback da CE é usado.

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

    ALTER DATABASE SCOPED CONFIGURATION SET CE_FEEDBACK = OFF;

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

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 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 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.

A partir do SQL Server 2022 (16.x), quando o Repositório de Consultas para réplicas secundárias está habilitado, o feedback da CE não tem reconhecimento de réplicas para réplicas secundárias em grupos de disponibilidade. Atualmente, o feedback da CE beneficia apenas as réplicas primárias. No failover, perde-se o feedback aplicado às réplicas primárias ou secundárias. Para obter mais informações, consulte Repositório de Consultas para réplicas secundárias.

Persistência para feedback da CE (estimativa de cardinalidade)

Aplica-se a: SQL Server (a partir do SQL Server 2022 (16.x))

O feedback da CE (estimativa de cardinalidade) pode detectar cenários em que a otimização da meta de linha deve ser persistente e manter essa alteração persistindo-a no repositório de consultas na forma de uma dica de repositório de consultas. A nova otimização será usada para execuções futuras da consulta. O feedback da CE persistirá em outros cenários fora 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 que são usados pelo modelo de correlação da CE e une cenários de predicados de junção que são manipulados pelo modelo de confinamento da CE.

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 160 ou superior, ou na dica QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n de 160 ou superior, e quando o Repositório de Consultas está habilitado no banco de dados e está no estado de “leitura/gravação”.

Problemas conhecidos do feedback da CE (estimativa de cardinalidade)

Problema Data descoberta Status Data resolvida
Desempenho lento do SQL Server depois de aplicar a Atualização Cumulativa 8 para SQL Server 2022 (16.x) sob determinadas condições. É possível encontrar uma utilização significativa da memória cache de planos com aumentos inesperados na utilização da CPU quando o feedback da CE está habilitado. Dezembro de 2023 Tem solução alternativa

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 RESOURCE_SEMAPHORE_QUERY_COMPILE também pode ser observado. É possível também notar aumentos constantes no número de objetos do Cache de Planos em uso que se aproximam dos limites do Cache de Planos e limpam manualmente o Cache de Planos com técnicas como ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE, DBCC FREESYSTEMCACHE ou DBCC FREEPROCCACHE que não fornecem assistência. Esse comportamento só foi observado por um pequeno número de 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 que foram qualificados para o recurso de feedback da CE para engajar. Durante o período em que o feedback da CE está analisando operadores de plano em que significativos erros de estimativa de modelo ocorreram, há um cenário no qual, durante essa fase de análise, um plano que estava sendo referenciado pode se tornar desreferenciado na memória, sem permitir que o plano seja posteriormente removido da memória por meio do algoritmo normal Menos Utilizado Recentemente (LRU). O mecanismo LRU é uma maneira pela qual o SQL Server impõe políticas de remoção de plano. O SQL Server também removerá planos da memória se o sistema estiver sob demanda de memória. Quando o SQL Server tenta remover os planos que foram desreferenciados incorretamente, ele não consegue remover esses planos do cache de planos, o que faz com que o cache continue a crescer. O cache crescente pode começar a causar compilações adicionais que, em última análise, usarão mais CPU e memória. Para obter mais informações, confira Internos do Cache de Planos.

Sintoma: o número de entradas em uso de cache de planos e marcadas como sujas de Planos SQL ou de Planos de Objeto aumenta com o tempo para 50 mil ou mais. Se você observar entradas de cache de planos que começam a se aproximar desse nível, juntamente com aumentos inesperados na utilização da CPU, seu sistema pode estar encontrando esse problema. Uma correção relacionada foi fornecida na Atualização Cumulativa 9 do SQL Server 2022 (16.x). Confira KB5030731. A correção tentou resolver um problema no qual as entradas de cache de planos são removidas quando o feedback da Estimativa de Cardinalidade (CE) tenta obter o perfil associado, o que causa um dano de memória. Correções adicionais para esse problema estarão disponíveis em uma próxima Atualização Cumulativa.

Para monitorar o número de entradas de cache de planos que seu sistema está usando, os exemplos a seguir podem ser usados como uma exibição pontual do número de entradas de cache de planos existentes. Como exemplo, observar o número de entradas de cache de planos marcadas como sujas periodicamente ao longo do tempo é uma maneira 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 fornecerá as mesmas informações do exemplo anterior, além de permitir que você observe métricas de desempenho adicionais. Os índices de ocorrência de Cache de Planos diminuirão, bem como o número de compilações em relação ao número de solicitações em lote/s. As consultas a seguir podem ser usadas para monitorar o sistema ao longo do tempo. Acompanhe a Taxa de Ocorrências no Cache (quedas inesperadas), os Objetos de Cache em uso (aumentos na contagem para níveis próximos a 50 mil sem diminuição) e um índice de Solicitações em lote/s menor do que o esperado em comparação a um aumento nas Compilações/s.

--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: o recurso Feedback da CE pode ser desabilitado no nível do banco de dados até que correções adicionais estejam disponíveis, se o sistema estiver enfrentando os sintomas descritos anteriormente. Para recuperar a memória do cache de planos que havia sido ocupada por esse problema, é necessária uma reinicialização da instância do SQL Server. Essa ação de reinicialização pode ser executada depois que o recurso de Feedback da CE é desabilitado. Para desabilitar o feedback da CE no nível do banco de dados, use a configuração no escopo do banco de dadosCE_FEEDBACK. 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