Estimativa de cardinalidade (SQL Server)

Aplica-se a:SQL Server Azure SQL Database Azure SQL Managed Instance Base de dados SQL no Microsoft Fabric

O SQL Server Query Optimizer é um Otimizador de Consulta baseado em custos. Isso significa que ele seleciona planos de consulta que têm o menor custo de processamento estimado para executar. 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, referido como a 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, uma cardinalidade melhorada leva a custos melhor estimados e, por sua vez, a planos de execução mais rápidos.

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

Nos seguintes casos, o SQL Server não pode calcular cardinalidades com precisão. Isso causa cálculos de custos imprecisos que podem causar planos de consulta subótimos. Evitar essas construções em consultas pode melhorar o desempenho da consulta. Às vezes, formulações de consulta alternativas ou outras medidas são possíveis e estas são apontadas:

Consultas com predicados que usam operadores de comparação entre colunas diferentes da mesma tabela.
Consultas com predicados que usam operadores e qualquer um dos seguintes itens são verdadeiros:
- Não há estatísticas sobre as colunas envolvidas em ambos os lados dos operadores.
- A distribuição de valores nas estatísticas não é uniforme, mas a consulta procura um conjunto de valores altamente seletivo. Esta situação pode ser especialmente verdadeira se o operador for qualquer outro operador que não seja o de igualdade (=).
- O predicado usa o operador de comparação não igual a (!=) ou o NOT operador lógico.
Consultas que usam qualquer uma das funções internas do SQL Server ou uma função definida pelo usuário com valor escalar cujo argumento não é um valor constante.
Consultas que envolvem a junção de colunas por meio de operadores aritméticos ou de concatenação de cadeia 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 CE para o seu sistema. A maioria dos sistemas se beneficia do CE mais recente porque é o mais preciso. O CE prevê quantas linhas 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 Consultas geralmente pode fazer um trabalho melhor de produzir um plano de consulta mais ideal.

Seu sistema de aplicação pode ter uma consulta importante cujo plano é alterado para um plano mais lento devido a mudanças no CE ao longo das versões. Você tem técnicas e ferramentas para identificar uma consulta que executa mais lentamente devido a problemas de CE. E você tem opções de como resolver os problemas de desempenho subsequentes.

Versões do CE

Em 1998, uma grande atualização do CE fazia parte do SQL Server 7.0, para o qual o nível de compatibilidade era 70. Esta versão do modelo CE baseia-se em quatro pressupostos básicos:

Independência: Presume-se que as distribuições de dados em diferentes colunas são independentes umas das outras, a menos que as informações de correlação estejam disponíveis e possam ser utilizadas.
Uniformidade: Valores distintos são espaçados uniformemente e todos eles têm a mesma frequência. Mais precisamente, dentro de cada etapa do histograma , valores distintos são distribuídos uniformemente e cada valor tem a mesma frequência.
Contenção (Simples): Os usuários consultam os dados existentes. Por exemplo, para uma junção de igualdade entre duas tabelas, leve em conta a seletividade de predicados¹ em cada histograma de entrada, antes de unir os histogramas para estimar a seletividade de junção.
Inclusão: Para predicados de filtro onde Column = Constant, presume-se que a constante realmente existe para a coluna associada. Se uma etapa de histograma correspondente não estiver vazia, presume-se que um dos valores distintos da etapa corresponde ao valor do predicado.

¹ Contagem de linhas que satisfaz o predicado.

As atualizações subsequentes começaram com o SQL Server 2014 (12.x), o que significa níveis de compatibilidade 120 e superiores. As atualizações do CE para os níveis 120 e superiores incorporam pressupostos e algoritmos atualizados que funcionam bem no armazenamento de dados moderno e em cargas de trabalho OLTP. A partir dos pressupostos do CE 70, os seguintes pressupostos do modelo foram alterados a partir do CE 120:

Independência torna-se Correlação: A combinação dos diferentes valores de coluna não é necessariamente independente. Isso pode se assemelhar mais a uma consulta de dados do mundo real.
Contenção simples torna-se Contenção de Base: os usuários podem consultar dados que não existem. Por exemplo, para uma junção de igualdade entre duas tabelas, usamos os histogramas das tabelas base para estimar a seletividade da junção e, em seguida, consideramos a seletividade dos predicados.

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

A partir do SQL Server 2016 (13.x), o Repositório de Consultas é uma ferramenta útil para examinar o desempenho de suas consultas. Quando o Repositório de Consultas estiver habilitado, ele começará a acompanhar o desempenho da consulta ao longo do tempo, mesmo que os planos de execução sejam alterados. Monitore o Repositório de Consultas para analisar o desempenho de consultas de alto custo ou regredido. Para obter mais informações, consulte Monitorar o desempenho usando o Repositório de Consultas.

Se estiver se preparando para uma atualização para o SQL Server ou promovendo um nível de compatibilidade de banco de dados em qualquer plataforma do SQL Server, considere Atualizar bancos de dados usando o Assistente de Ajuste de Consulta, que pode ajudar a comparar o desempenho da consulta em dois níveis de compatibilidade diferentes.

Important

Verifique se o Repositório de Consultas está configurado corretamente para seu banco de dados e carga de trabalho. Para obter mais informações, consulte Práticas recomendadas para monitorar cargas de trabalho com o 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 chamado 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 você possa alterar o caminho). Quando você abre o arquivo .xel no Management Studio, suas informações detalhadas são exibidas de maneira 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

Note

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

Para obter informações sobre eventos estendidos personalizados para o Banco de dados SQL, consulte Eventos estendidos no Banco de dados SQL.

Passos para avaliar a versão CE

Seguem-se os passos que pode utilizar para avaliar se alguma das suas consultas mais importantes tem um desempenho pior no CE mais recente. Algumas das etapas são executadas executando um exemplo de código apresentado em uma seção anterior.

Abra o SQL Server Management Studio (SSMS). Verifique se o banco de dados do SQL Server está definido para o nível de compatibilidade mais alto disponível.
Execute as seguintes etapas preliminares:
1. Abra o SQL Server Management Studio (SSMS).
2. Execute o Transact-SQL para garantir que seu banco de dados do SQL Server esteja definido para o nível de compatibilidade mais alto disponível.
3. Certifique-se de que seu banco de dados tenha sua LEGACY_CARDINALITY_ESTIMATION configuração ativada OFF.
4. Limpe o seu Repositório de Consultas. No banco de dados, verifique se o Repositório de Consultas está ATIVADO.
5. Execute a instrução: SET NOCOUNT OFF;
Execute a instrução: SET STATISTICS XML ON;
Execute a sua consulta importante.
No painel de resultados, na guia Mensagens , observe o número real de linhas afetadas.
No painel de resultados na guia Resultados , clique duas vezes na célula que contém as estatísticas no formato XML. Um plano de consulta gráfica é exibido.
Clique com o botão direito do rato na primeira caixa do plano de consulta gráfica e, em seguida, selecione Propriedades.
Para comparação posterior com uma configuração diferente, observe os valores para as seguintes propriedades:
- CardinalityEstimationModelVersion.
- Número estimado de linhas.
- Custo de entrada/saída estimado e várias propriedades semelhantes estimadas que envolvem desempenho real do sistema em vez de previsões de número de linhas.
- Operação Lógica e Operação Física. O paralelismo é um bom valor.
- Modo de execução real. Batch é um bom valor, melhor do que Row.
Compare o número estimado de linhas com o número real de linhas. O CE é impreciso em 1% (alto ou baixo), ou em 10%?
Executar: SET STATISTICS XML OFF;
Execute o Transact-SQL para diminuir o nível de compatibilidade do banco de dados em um nível (como de 130 para 120).
Execute novamente todas as etapas não preliminares.
Compare os valores da propriedade CE das duas execuções.
- A percentagem de imprecisão no CE mais recente é menor do que no CE mais antigo?
Finalmente, compare os vários valores de propriedades de desempenho das duas execuções.
- A sua consulta utilizou um plano diferente no âmbito das duas diferentes estimativas de CE?
- A sua consulta foi executada mais lentamente no CE mais recente?
- A menos que sua consulta funcione melhor e com um plano diferente com a versão anterior do CE, você provavelmente vai querer o CE mais recente.
- No entanto, se a sua consulta for executada com um plano mais rápido sob o CE mais antigo, considere forçar o sistema a usar o plano mais rápido e ignorar o CE. Desta forma, pode ter a versão mais recente do CE para tudo, mantendo o plano mais rápido no caso excecional.

Como ativar o melhor plano de consulta

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

Você pode definir o nível de compatibilidade do banco de dados para um valor inferior ao mais recente disponível, para todo o seu banco de dados.
- Por exemplo, definir o nível de compatibilidade 110 ou inferior ativa o CE 70, mas torna todas as consultas sujeitas ao modelo CE anterior.
- Além disso, definir um nível de compatibilidade mais baixo também perde uma série de melhorias no otimizador de consulta para as versões mais recentes e afeta todas as consultas no banco de dados.
Você pode usar LEGACY_CARDINALITY_ESTIMATION a opção de configuração de escopo do banco de dados, para que todo o banco de dados use o CE mais antigo, mantendo outras melhorias no otimizador de consulta.
Você pode usar uma dica de consulta LEGACY_CARDINALITY_ESTIMATION para que uma única consulta use o CE mais antigo, enquanto mantém outras melhorias no otimizador de consulta.
Você pode impor o LEGACY_CARDINALITY_ESTIMATION recurso de dica através do Repositório de Consultas, para que uma única consulta use o CE mais antigo sem alterar a consulta.
Forçar um plano diferente com o Repositório de Consultas.

Nível de compatibilidade do banco de dados

Você pode garantir que seu banco de dados esteja em um nível específico usando o seguinte código de Transact-SQL para o nível de compatibilidade ALTER DATABASE (Transact-SQL).

Important

Os números de versão do mecanismo de banco de dados para SQL Server e Banco de Dados SQL do Azure não são comparáveis entre si e, em vez disso, são números de compilação internos para esses produtos separados. O mecanismo de banco de dados do Azure SQL Server é baseado na mesma base de código que o mecanismo de banco de dados do SQL Server. Mais importante ainda, o mecanismo de banco de dados no Banco de Dados SQL do Azure sempre tem os bits mais recentes do mecanismo de banco de dados SQL. A versão 12 da Base 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 de Banco de Dados para bancos de dados existentes. Cabe aos clientes fazê-lo a seu critério.

SELECT ServerProperty('ProductVersion');
GO

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

Para bancos de dados pré-existentes executados em níveis de compatibilidade mais baixos, desde que o aplicativo não precise usar aprimoramentos que só estão disponíveis em um nível de compatibilidade de banco de dados mais alto, é uma abordagem válida manter o nível de compatibilidade de banco de dados anterior. Para novos trabalhos de desenvolvimento, ou quando um aplicativo existente exigir o uso de novos recursos, como processamento inteligente de consultas em bancos de dados SQL, bem como alguns novos Transact-SQL, planeje atualizar o nível de compatibilidade do banco de dados para o mais recente disponível. Para obter mais informações, consulte Níveis de compatibilidade e atualizações do Mecanismo de Banco de Dados.

Caution

Antes de alterar o nível de compatibilidade do banco de dados, revise o nível de compatibilidade ALTER DATABASE (Transact-SQL).

ALTER DATABASE <yourDatabase>
SET COMPATIBILITY_LEVEL = 150;
GO

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

Avaliador de cardinalidade legado

Para um banco de dados SQL Server definido no nível de compatibilidade 120 e superior, o estimador de cardinalidade herdado (CE versão 70) pode ser ativado no nível do banco de dados usando o 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 consulta para usar dica

A partir do SQL Server 2016 (13.x) SP1, modifique a consulta para usar a Dica de ConsultaUSE 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 do Repositório de Consultas

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

Identifique a consulta nas vistas 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%';

O exemplo a seguir aplica uma sugestão do Query Store para forçar o estimador 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''))';
```

Note

Para obter mais informações, consulte Dicas do Repositório de Consultas (Visualização). 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 melhor controle, você pode forçar o sistema a usar o plano que foi gerado com o CE 70 durante o teste. Depois de fixar o seu plano preferido, pode definir toda a sua base de dados para usar o nível de compatibilidade e CE mais recentes. A opção é elaborada a seguir.

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

Executar sys.sp_query_store_force_plan.
No SQL Server Management Studio (SSMS), expanda o nó Repositório de Consultas, clique com o botão direito em Principais Nodos Consumidores de Recursos e selecione Exibir Principais Nodos Consumidores de Recursos. O visor mostra botões rotulados como Force Plan e Unforce Plan.

Para obter mais informações sobre o Repositório de Consultas, consulte Monitorar o desempenho usando o Repositório de Consultas.

Dobragem constante e avaliação da expressão durante a Estimativa da Cardinalidade

O Mecanismo de Banco de Dados avalia algumas expressões constantes antecipadamente para melhorar o desempenho da consulta. Isto é referido como dobragem constante. Uma constante é um Transact-SQL literal, como 3, 'ABC', '2005-12-31', 1.0e3, ou 0x12345678. Para obter mais informações, consulte Dobragem constante.

Além disso, algumas expressões que não são dobradas em constantes, mas cujos argumentos são conhecidos em tempo de compilação, sejam os argumentos parâmetros ou constantes, são avaliadas pelo estimador de tamanho do conjunto de resultados (cardinalidade) que faz parte do Otimizador de Consultas durante a otimização. Para obter mais informações, consulte Avaliação de expressão.

Práticas recomendadas: Usando dobramento constante e avaliação de expressão em tempo de compilação para gerar planos de consulta ideais

Para garantir que você gere planos de consulta ideais, é melhor projetar consultas, procedimentos armazenados e lotes para que o Otimizador de Consultas possa estimar com precisão a seletividade das condições em sua consulta, com base em estatísticas sobre sua distribuição de dados. Caso contrário, o Otimizador de Consulta deve usar uma estimativa padrão ao estimar a seletividade.

Para certificar-se de que o Estimador de cardinalidade do Otimizador de Consulta fornece boas estimativas, você deve primeiro certificar-se de que as AUTO_CREATE_STATISTICS opções e AUTO_UPDATE_STATISTICS banco de dados SET são ON (a configuração padrão) ou que você criou manualmente estatísticas em todas as colunas referenciadas em uma condição de consulta. Em seguida, quando estiver desenvolvendo as condições nas suas consultas, execute o seguinte sempre que possível:

Evite o uso de variáveis locais em consultas. Em vez disso, use parâmetros, literais ou expressões na consulta.
Limite o uso de operadores e funções incorporados em uma consulta que contém um parâmetro aos listados em Avaliação de Expressão Compile-Time para Estimativa de Cardinalidade.
Certifique-se de que as expressões somente constantes na condição da sua consulta são dobráveis constantemente ou podem ser avaliadas no momento da compilação.
Se você 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, pode ser útil executar uma das seguintes opções:
- 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 procedure em vez de uma variável local.
- Construa uma cadeia de caracteres que contenha uma consulta baseada em parte no valor da variável local e, em seguida, execute a cadeia de caracteres usando SQL dinâmico (EXEC ou, de preferência sp_executesql).
- Parametrize a consulta e execute-a usando sp_executesql, e passe o valor da variável como um parâmetro para a consulta.

Exemplos de melhorias da Conformidade Europeia (CE)

Esta seção descreve consultas de exemplo que se beneficiam dos aprimoramentos implementados no CE em versões recentes. Esta é uma informação de fundo que não exige uma ação específica da sua parte.

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

Suponhamos que as estatísticas foram reunidas pela última vez para OrderTable em 2016-04-30, quando o máximo OrderAddedDate foi 2016-04-30. O CE 120 (e versão superior) entende que as colunas em OrderTable, que têm dados ascendentes podem ter valores maiores do que o máximo registado pelas estatísticas. Essa compreensão melhora o plano de consulta para Transact-SQL SELECT instruções como as seguintes.

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

Exemplo B. CE entende que predicados filtrados na mesma tabela são frequentemente correlacionados

A seguir SELECT , vemos predicados filtrados em Model e ModelVariant. Intuitivamente, entendemos que quando Model é 'Xbox' há uma chance de ser 'One', dado que o ModelVariant Xbox tem uma variante chamada One.

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

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

Exemplo C. CE já não assume qualquer correlação entre predicados filtrados de tabelas diferentes

Uma nova pesquisa extensiva sobre cargas de trabalho modernas e dados de negócios reais revela que os filtros de predicados de tabelas diferentes geralmente não se correlacionam entre si. Na consulta a seguir, o CE assume que não há correlação entre s.type e r.date. Portanto, o CE faz uma estimativa menor 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';

Comentários

Esta página foi útil?

Last updated on 2025-11-18