Feedback da CE (estimativa de cardinalidade)

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

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. O feedbackda CE identifica e usa 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. O feedback é aplicado 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 há 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 de banco de dados com escopo CE_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 é 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, confira Dicas de 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: Aplica-se a: SQL Server 2022 (16.x) e versões posteriores, Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure.

Os comentários de CE, estimativa de cardinalidade, podem detectar cenários em que a otimização da meta de linha deve ser mantida 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 da CE persiste 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)

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 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 LRU (Least Recently Used) usual. O mecanismo LRU é uma maneira pela qual o SQL Server impõe políticas de remoção de plano. O SQL Server também remove os 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, 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, confira 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 as entradas do cache de planos que começam a se aproximar desse nível em conjunto com os aumentos inesperados na utilização 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 o artigo KB5033663 da base de dados de conhecimento.

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. Os índices de ocorrência de Cache de Planos diminuem, 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. Vigiar a Proporção de Ocorrência no Cache (quedas não antecipadas), os Objetos de Cache em uso (aumentos na contagem para níveis que se aproximam de 50 mil sem diminuir) e uma proporção de Solicitações em Lote por segundo menor do que o esperado em comparação com um aumento nas 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 a enfrentar os sintomas descritos anteriormente, depois que da aplicação da atualização cumulativa 12 KB5033663, o recurso de comentários CE poderá ser desabilitado no nível do 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 da CE no nível do banco de dados, use a configuração de banco de dados com escopo CE_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

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