Estimativa de cardinalidade (SQL Server)

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

O otimizador de consulta do SQL Server é um otimizador de consulta baseado no custo. Isso significa que ele seleciona planos de consulta com o custo de processamento estimado mais baixo para ser executado. O Otimizador de Consulta determina o custo de execução de um plano de consulta com base em dois fatores principais:

• O número total de linhas processadas em cada nível de um plano de consulta, chamado cardinalidade do plano.
• O modelo de custo do algoritmo ditado pelos operadores usados na consulta.

O primeiro fator, cardinalidade, é usado como um parâmetro de entrada do segundo fator, o modelo de custo. Portanto, a cardinalidade aprimorada gera custos melhor estimados e, consequentemente, planos de execução mais rápidos.

A CE (estimativa de cardinalidade) no SQL Server é derivada principalmente de histogramas criados quando índices ou estatísticas são criados, manualmente ou automaticamente. Às vezes, o SQL Server também usa informações de restrição e novas consultas lógicas para determinar a cardinalidade.

Nos casos a seguir, o SQL Server não consegue calcular cardinalidades com precisão. Isso gera cálculos de custo inexatos que podem causar planos de consulta menos favoráveis. Evitar tais construções nas consultas pode melhorar o desempenho da consulta. Às vezes, formulações de consulta alternativas ou outras medidas são possíveis e são indicadas:

• Consultas com predicados que usam operadores de comparação entre colunas diferentes da mesma tabela.
• Consultas com predicados que usam operadores e qualquer um destes itens são true:
  • Não há nenhuma estatística referente às colunas envolvidas em nenhum dos lados dos operadores.
  • A distribuição de valores nas estatísticas não é uniforme, mas a consulta busca um conjunto de valores altamente seletivo. Essa situação pode ser especialmente verdadeira se o operador não for o operador de igualdade (=).
  • O predicado usa o operador de comparação diferente de (! =) ou o operador lógico NOT.
• Consultas que usam as funções internas do SQL Server ou uma função com valor escalar, definida pelo usuário, cujo argumento não é um valor constante.
• Consultas que envolvem colunas de associação por aritmética ou operadores de concatenação de cadeias de caracteres.
• Consultas que comparam variáveis cujos valores não são conhecidos quando a consulta é compilada e otimizada.

Este artigo ilustra como você pode avaliar e escolher a melhor configuração de CE para seu sistema. A maior parte dos sistemas se beneficia da CE mais recente, pois ela é a mais precisa. A CE prevê o número de linhas que sua consulta provavelmente retornará. A previsão de cardinalidade é usada pelo Otimizador de Consulta para gerar o plano de consulta ideal. Com estimativas mais precisas, o Otimizador de Consulta normalmente pode fazer um trabalho melhor de produção de um plano de consulta melhor.

Seu sistema de aplicativos poderia ter uma consulta importante cujo plano foi alterado para um plano mais lento devido às alterações na CE ao longo das versões. Você tem técnicas e ferramentas para identificar uma consulta com desempenho mais lento devido a problemas na CE. Além disso, você tem opções para resolver problemas de desempenho decorrentes disso.

Versões da CE

Em 1998, uma atualização importante da CE fazia parte do SQL Server 7.0, para o qual o nível de compatibilidade era 70. Essa versão do modelo da CE é definida em quatro suposições básicas:

• Independência: as distribuições de dados em diferentes colunas devem ser independentes entre si, a menos que as informações de correlação estejam disponíveis e sejam utilizáveis.

• Uniformidade: diferentes valores são espaçados uniformemente e todos eles têm a mesma frequência. Mais precisamente, dentro de cada etapa de histograma, diferentes valores são distribuídos uniformemente e cada valor tem a mesma frequência.

• Confinamento (simples): os usuários consultam dados existente. Por exemplo, para uma junção de igualdade entre duas tabelas, considere a seletividade de predicados¹ em cada histograma de entrada antes de ingressar histogramas para estimar a seletividade da junção.

• Inclusão: para filtrar predicados em que Column = Constant, supõe-se que a constante realmente exista para a coluna associada. Se uma etapa do histograma correspondente não estiver vazia, presume-se que um dos valores diferentes da etapa corresponda ao valor do predicado.

  ¹ Contagem de linhas que satisfaz o predicado.

Atualizações posteriores iniciadas com o SQL Server 2014 (12.x), significando níveis de compatibilidade 120 e superiores. As atualizações da CE dos níveis 120 e acima incorporam suposições e algoritmos atualizados que funcionam bem em data warehousing moderno e em cargas de trabalho OLTP. De suposições da CE 70, as seguintes suposições de modelo foram alteradas a partir da CE 120:

• A independência se torna correlação: a combinação dos diferentes valores de coluna não é necessariamente independente. Isso pode se parecer com uma consulta de dados mais real.
• O confinamento simples torna-se confinamento básico: os usuários podem consultar dados que não existem. Por exemplo, para que haja uma junção de igualdade entre duas tabelas, usamos histogramas de tabelas básicos para calcular a seletividade da junção e, sem seguida, consideramos a seletividade de predicados.

Usar o Repositório de Consultas para avaliar a versão CE

Desde o SQL Server 2016 (13.x), o Repositório de Consultas é uma ferramenta útil para examinar o desempenho das consultas. Quando o Repositório de Consultas estiver habilitado, ele começará a acompanhar o desempenho de consultas ao longo do tempo, mesmo se os planos de execução mudarem. Monitore o Repositório de Consultas para desempenho de consultas de alto custo ou regredidas. Para obter mais informações, consulte Monitoramento de desempenho usando o Repositório de consultas.

Se você estiver se preparando para uma atualização para SQL Server ou para promover um nível de compatibilidade do banco de dados em qualquer plataforma do SQL Server, considere a possibilidade de atualizar bancos de dados usando o assistente de ajuste de consulta, que pode ajudar a comparar o desempenho de consulta em dois níveis de compatibilidade diferentes.

Importante

Verifique se a Repositório de Consultas está configurado corretamente para seu banco de dados e carga de trabalho. Para obter mais informações, consulte Melhores práticas com Repositório de Consultas.

Usar eventos estendidos para avaliar a versão CE

Outra opção para acompanhar o processo de estimativa de cardinalidade é usar o evento estendido denominado query_optimizer_estimate_cardinality. O exemplo de código Transact-SQL a seguir é executado no SQL Server. Ele grava um arquivo .xel em C:\Temp\ (embora o caminho possa ser alterado). Quando você abre o arquivo .xel no Management Studio, as informações detalhadas são exibidas de forma amigável.

DROP EVENT SESSION Test_the_CE_qoec_1 ON SERVER;  
go  
  
CREATE EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
ADD EVENT sqlserver.query_optimizer_estimate_cardinality  
 (  
 ACTION (sqlserver.sql_text)  
  WHERE (  
  sql_text LIKE '%yourTable%'  
  and sql_text LIKE '%SUM(%'  
  )  
 )  
ADD TARGET package0.asynchronous_file_target
 (SET  
  filename = 'c:\temp\xe_qoec_1.xel',  
  metadatafile = 'c:\temp\xe_qoec_1.xem'  
 );  
GO  
  
ALTER EVENT SESSION Test_the_CE_qoec_1  
ON SERVER  
STATE = START;  --STOP;  
GO  

Observação

O evento sqlserver.query_optimizer_estimate_cardinality não está disponível para o Banco de Dados SQL do Azure.

Veja informações sobre eventos estendidos adaptados ao banco de dados SQL em Eventos estendidos no Banco de Dados SQL.

Etapas para avaliar a versão da CE

Veja a seguir as etapas que você pode usar para avaliar se qualquer uma das suas consultas mais importantes tem um desempenho inferior na CE mais recente. Algumas das etapas são realizadas com a execução de um exemplo de código apresentado em uma seção anterior.

1. Abra o SQL Server Management Studio (SSMS). Verifique se o banco de dados do SQL Server está definido com o nível de compatibilidade mais alto disponível.

2. Realize as seguintes etapas preliminares:

   1. Abra o SQL Server Management Studio (SSMS).

   2. Execute o Transact-SQL para garantir que o banco de dados do SQL Server seja definido com o nível de compatibilidade mais alto disponível.

   3. Verifique se o seu banco de dados tem a configuração LEGACY_CARDINALITY_ESTIMATION como OFF.

   4. Limpe seu Repositório de Consultas. Verifique se o Repositório de Consultas está HABILITADO em seu banco de dados.

   5. Execute a instrução: SET NOCOUNT OFF;

3. Execute a instrução: SET STATISTICS XML ON;

4. Execute a consulta importante.

5. No painel de resultados, na guia Mensagens , observe o número real de linhas afetadas.

6. No painel de resultados, na guia Resultados , clique duas vezes na célula que contém as estatísticas em formato XML. Um plano de consulta gráfico é exibido.

7. Clique com o botão direito do mouse no plano de consulta gráfico e selecione Propriedades.

8. Para a comparação posterior com uma configuração diferente, observe os valores das seguintes propriedades:

   • CardinalityEstimationModelVersion.

   • Número Estimado de Linhas.

   • Estimated I/O Coste várias propriedades Estimated semelhantes que envolvem o desempenho real em vez de previsões de contagem de linha.

   • Operação Lógica e Operação Física. Parallelism é um valor aceitável.

   • Modo de Execução Real. Batch é um valor aceitável, melhor que Row.

9. Compare o número estimado de linhas com o número real de linhas. A CE não é precisa em % 1 (alto ou baixo) ou 10%?

10. Execute: SET STATISTICS XML OFF;

11. Execute o Transact-SQL para diminuir o nível de compatibilidade do banco de dados em um nível (por exemplo, de 130 para 120).

12. Execute novamente todas as etapas não preliminares.

13. Compare os valores de propriedade da CE das duas execuções.

    • O percentual de imprecisão na CE mais recente está abaixo da CE mais antiga?

14. Por fim, compare vários valores de propriedade de desempenho das duas execuções.

    • Sua consulta usou um plano diferente nas duas estimativas de CE diferentes?

    • A consulta foi executada mais lentamente na CE mais recente?

    • A menos que a consulta tenha um desempenho melhor e com um plano diferente na CE mais antiga, certamente, será recomendável a CE mais recente.

    • No entanto, se sua consulta for executada com um plano mais rápido na CE mais antiga, considere forçar o sistema para usar o plano mais rápido e ignorar a CE. Dessa forma, você pode ter a CE mais recente para tudo, ao mesmo tempo que mantém o plano mais rápido por via das dúvidas.

Como ativar o melhor plano de consulta

Suponha que, com a CE 120 ou superior, um plano de consulta menos eficiente é gerado para sua consulta. Aqui estão algumas opções que você tem para ativar o melhor plano, ordenadas do maior escopo para o menor:

• Você pode definir o nível de compatibilidade de todo o seu banco de dados com um valor menor do que o valor mais recente disponível.

  • Por exemplo, configurar o nível de compatibilidade 110 ou inferior ativa a CE 70, mas deixa todas as consultas sujeitas ao modelo de CE anterior.

  • Além disso, configurar um nível de compatibilidade inferior também priva você de diversos aprimoramentos no otimizador de consulta que estão presentes nas versões mais recentes, afetando todas as consultas realizadas no banco de dados.

• Você poderá usar a opção de configuração no escopo do banco de dados LEGACY_CARDINALITY_ESTIMATION para fazer com que todo o banco de dados use a CE mais antiga, mantendo outras melhorias no otimizador de consulta.

• Você poderá usar a dica de consulta LEGACY_CARDINALITY_ESTIMATION para fazer com que uma única consulta use a CE mais antiga, mantendo outras melhorias no otimizador de consulta.

• Você pode impor o LEGACY_CARDINALITY_ESTIMATION por meio do recurso de dica do Repositório de Consultas, para que uma consulta individual use a CE mais antiga sem alterar a consulta.

• Force um plano diferente com o Repositório de Consultas.

Nível de compatibilidade do banco de dados

É possível garantir se o banco de dados está em determinado nível usando o seguinte código Transact-SQL para COMPATIBILITY_LEVEL.

Importante

Os números de versão do mecanismo de banco de dados para o SQL Server e o Banco de Dados SQL do Azure não são comparáveis entre si, mas números de build internos para esses produtos separados. O mecanismo de banco de dados para o SQL Server do Azure se baseia na mesma base de código do mecanismo de banco de dados do SQL Server. O mais importante é que o mecanismo de banco de dados do Banco de Dados SQL do Azure sempre tem os bits mais recentes do mecanismo de banco de dados SQL. A versão 12 do Banco de Dados SQL do Azure é mais recente do que a versão 15 do SQL Server. A partir de novembro de 2019, no Banco de Dados SQL do Azure, o nível de compatibilidade padrão é 150 para bancos de dados recém-criados. A Microsoft não atualiza o nível de compatibilidade do banco de dados dos bancos de dados existentes. É responsabilidade dos clientes fazer isso a seu critério.

SELECT ServerProperty('ProductVersion');  
GO  

SELECT d.name, d.compatibility_level  
FROM sys.databases AS d  
WHERE d.name = 'yourDatabase';  
GO  

No caso dos bancos de dados preexistentes em execução em níveis de compatibilidade mais baixos, contanto que o aplicativo não precise usar aprimoramentos que estejam disponíveis apenas em um nível de compatibilidade do banco de dados mais alto, é uma abordagem válida manter o nível de compatibilidade do banco de dados anterior. Para um novo trabalho de desenvolvimento ou quando um aplicativo existente exigir o uso de novos recursos, como Processamento Inteligente de Consulta, bem como alguns novos Transact-SQL, planeje atualizar o nível de compatibilidade do banco de dados para o mais recente disponível. Para saber mais, confira Níveis de compatibilidade e atualizações de Mecanismo de Banco de Dados.

Cuidado

Antes de alterar o nível de compatibilidade do banco de dados, examine as Melhores práticas para atualizar o nível de compatibilidade do banco de dados.

ALTER DATABASE <yourDatabase>  
SET COMPATIBILITY_LEVEL = 150;  
GO  

Para um banco de dados do SQL Server definido no nível de compatibilidade 120 ou acima, a ativação do sinalizador de rastreamento 9481 força o sistema a usar o CE versão 70.

Avaliador de cardinalidade herdada

Para obter um banco de dados do SQL Server definido no nível de compatibilidade 120 e acima, o avaliador de cardinalidade herdada (versão 70 do CE) poderá ser ativado no nível do banco de dados usando ALTER DATABASE SCOPED CONFIGURATION.

ALTER DATABASE SCOPED CONFIGURATION 
SET LEGACY_CARDINALITY_ESTIMATION = ON;  
GO  
  
SELECT name, value  
FROM sys.database_scoped_configurations  
WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';  
GO

Modificar a consulta para usar dica

Começando com o SQL Server 2016 (13.x) SP1, modifique a consulta para usar a Dica de Consulta USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION').

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01'
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));  

Definir uma dica de Repositório de Consultas

As consultas podem ser forçadas a usar o avaliador de cardinalidade herdado sem modificar a consulta, usando dicas de Repositório de Consultas.

1. Identifique a consulta nas exibições de catálogo sys.query_store_query_text e sys.query_store_query do Repositório de Consultas. Por exemplo, procure uma consulta executada por fragmento de texto:

   SELECT q.query_id, qt.query_sql_text
   FROM sys.query_store_query_text qt 
   INNER JOIN sys.query_store_query q ON 
   qt.query_text_id = q.query_text_id 
   WHERE query_sql_text like N'%ORDER BY ListingPrice DESC%'  
   AND query_sql_text not like N'%query_store%';
   

2. O exemplo a seguir aplica uma dica do Repositório de Consultas para forçar o avaliador de cardinalidade herdado em query_id 39, sem modificar a consulta:

   EXEC sys.sp_query_store_set_hints @query_id= 39, @query_hints = N'OPTION(USE HINT(''FORCE_LEGACY_CARDINALITY_ESTIMATION''))';
   

Observação

Para obter mais informações, confira Dicas da história da consulta (versão prévia). Atualmente, esse recurso está disponível apenas no Banco de Dados SQL do Azure.

Como forçar um plano de consulta específico

Para obter o controle mais refinado, você pode forçar o sistema a usar o plano gerado com a CE 70 durante o teste. Depois de fixar seu plano preferido, você poderá definir o banco de dados inteiro para usar o nível de compatibilidade e a CE mais recentes. A opção é elaborada a seguir.

O Repositório de Consultas oferece diferentes maneiras pelas quais você pode forçar o sistema a usar um plano de consulta específico:

• Execute sys.sp_query_store_force_plan.

• No SSMS (SQL Server Management Studio), expanda o nó Repositório de Consultas, clique com o botão direito do mouse em Nós que Consomem Mais Recursos e clique em Exibir os Nós que Consomem Mais Recursos. A exibição mostra os botões rotulados Forçar Plano e Não Forçar Plano.

Para obter mais informações sobre o Repositório de Consultas, confira Monitorando o desempenho com o Repositório de Consultas.

Dobra constante e avaliação de expressões durante a estimativa da cardinalidade

O mecanismo de banco de dados avalia algumas expressões constantes antecipadamente para aprimorar o desempenho de consulta. Isto é chamado de dobra constante. Uma constante é um literal Transact-SQL, como 3, 'ABC', '2005-12-31', 1.0e3 ou 0x12345678. Para obter mais informações, confira Dobra constante.

Além disso, algumas expressões que não são constantes dobráveis, mas cujos argumentos são conhecidos no tempo de compilação, sejam esses argumentos parâmetros ou constantes, são avaliadas pelo avaliador de tamanho do conjunto de resultados (cardinalidade) que faz parte do Otimizador de Consulta durante a otimização. Para obter mais informações, confira Avaliação de expressão.

Melhores práticas: Como usar dobra constante e avaliação de expressão em tempo de compilação para gerar planos de consulta ideais

Para realmente gerar planos de consulta ideais, é bom projetar consultas, procedimentos armazenados e lotes de modo que o Otimizador de Consulta possa calcular com precisão a seletividade das condições na consulta, com base em estatísticas sobre a distribuição de dados. Caso contrário, o Otimizador de Consulta precisa usar uma estimativa padrão ao estimar a seletividade.

Para ter certeza de que o Avaliador de Cardinalidade do Otimizador de Consulta fornece boas estimativas, você deve primeiramente ter certeza de que as opções SET AUTO_CREATE_STATISTICS e AUTO_UPDATE_STATISTICS do banco de dados estão ON (a configuração padrão), ou que você criou estatísticas manualmente em todas as colunas mencionadas na condição da consulta. Então, quando você estiver projetando as condições das consultas, faça o seguinte quando possível:

• Evite usar variáveis locais em consultas. No lugar, use parâmetros, literais ou expressões na consulta.

• Limite o uso de operadores e funções incorporadas em uma consulta que contém um parâmetro para os que estão listados em Avaliação de expressão em tempo de compilação para estimativa de cardinalidade.

• Certifique-se de que as expressões somente constantes da condição da consulta sejam dobráveis por constante ou possam ser avaliadas no tempo de compilação.

• Se precisar usar uma variável local para avaliar uma expressão a ser usada em uma consulta, considere avaliá-la em um escopo diferente da consulta. Por exemplo, a execução de uma das seguintes opções pode ajudar:

  • Passe o valor da variável para um procedimento armazenado que contenha a consulta que você deseja avaliar e faça com que a consulta use o parâmetro de procedimento em vez de uma variável local.

  • Crie uma cadeia de caracteres contendo uma consulta baseada em parte do valor da variável local e, então, execute a cadeia de caracteres usando SQL dinâmico (EXEC ou, preferencialmente, sp_executesql).

  • Atribua parâmetros à consulta e execute-a usando sp_executesql. Depois, passe o valor da variável como um parâmetro para a consulta.

Exemplos de melhorias de CE

Esta seção descreve consultas de exemplo que se beneficiam das melhorias implementadas na CE em versões recentes. Essas são as informações básicas que não exigem ação específica da sua parte.

Exemplo A. A CE entende que o valor máximo pode ser maior do que quando as estatísticas foram coletadas pela última vez

Suponha que as estatísticas foram coletadas pela última vez para OrderTable em 2016-04-30, quando o OrderAddedDate máximo era 2016-04-30. A CE 120 (e versões superiores) entende que as colunas em OrderTable, que têm dados crescentes, podem ter valores maiores do que o máximo registrado pelas estatísticas. Esse entendimento aprimora o plano de consulta de instruções Transact-SQL SELECT como a seguinte.

SELECT CustomerId, OrderAddedDate  
FROM OrderTable  
WHERE OrderAddedDate >= '2016-05-01';  

Exemplo B. A CE entende que os predicados filtrados na mesma tabela estão frequentemente correlacionados

Na SELECT a seguir, vemos predicados filtrados em Model e em ModelVariant. Intuitivamente, entendemos que quando Model é 'Xbox' há uma possibilidade de que ModelVariant seja 'One', pois o Xbox tem uma variante chamada One.

A partir da CE 120, O SQL Server entende que pode haver uma correlação entre as duas colunas na mesma tabela, Model e ModelVariant. A CE faz uma estimativa mais precisa de quantas linhas serão retornadas pela consulta, e o otimizador de consulta gera um plano melhor.

SELECT Model, Purchase_Price  
FROM dbo.Hardware  
WHERE Model = 'Xbox' AND  
ModelVariant = 'Series X';  

Exemplo C. A CE não pressupõe mais nenhuma correlação entre os predicados filtrados de tabelas diferentes

Novas pesquisas extensivas sobre cargas de trabalho modernas e dados de negócios reais revelam que os filtros de predicado de tabelas diferentes geralmente não se correlacionam entre si. Na consulta a seguir, a CE pressupõe que não há nenhuma correlação entre s.type e r.date. Portanto, a CE faz uma estimativa inferior do número de linhas retornadas.

SELECT s.ticket, s.customer, r.store  
FROM dbo.Sales AS s  
CROSS JOIN dbo.Returns AS r  
WHERE s.ticket = r.ticket AND  
s.type = 'toy' AND  
r.date = '2016-05-11';  

Conteúdo relacionado

• Feedback da CE (estimativa de cardinalidade)
• Monitorar e ajustar o desempenho
• Otimizar os planos de consulta com o avaliador de cardinalidade do SQL Server 2014
• Dicas (Transact-SQL) – Consulta
• Dicas de consulta USE HINT
• Atualizar bancos de dados usando o assistente de ajuste de consulta
• Monitorar o desempenho usando o Repositório de Consultas
• Guia de arquitetura de processamento de consultas
• Dicas do Repositório de Consultas
• Processamento inteligente de consultas em bancos de dados SQL
• DBCC TRACEON: sinalizadores de rastreamento (Transact-SQL)
• sys.query_store_query_hints