Compartilhar via

A suposição de contenção de junção no Novo Avaliador de Cardinalidade degrada o desempenho da consulta

  • Artigo

Este artigo ajuda você a resolver problemas de desempenho que podem ocorrer no SQL Server 2014 e versões posteriores quando você compila suas consultas usando o novo avaliador de cardinalidade.

Versão original do produto: SQL Server
Número original do KB: 3189675

Sintomas

Considere o cenário a seguir.

  • Você está usando o SQL Server 2014 ou uma versão posterior.
  • Você executa uma consulta que contém predicados de filtro de junções e não junções.
  • Você compila a consulta usando a nova Estimativa de Cardinalidade (SQL Server) (Nova CE).

Nesse cenário, você experimenta degradação do desempenho da consulta.

Esse problema não ocorrerá se você compilar a consulta usando o CE herdado.

Causa

A partir do SQL Server 2014, o Novo Avaliador de Cardinalidade (Novo CE) foi introduzido para o nível de compatibilidade do banco de dados 120 e superior. O Novo CE altera várias suposições do CE herdado no modelo usado pelo Otimizador de Consulta quando estima a cardinalidade para diferentes operadores e predicados.

Uma dessas mudanças está relacionada à suposição de contenção de junção.

O modelo CE herdado pressupõe que os usuários sempre consultam os dados existentes. Isso significa que, para um predicado de junção que envolve uma operação de junção por equidefinição para duas tabelas, as colunas unidas existem em ambos os lados da junção. Na presença de predicados de filtro de não junção adicionais na tabela de junção, o CE herdado pressupõe algum nível de correlação para os predicados de filtro de junção e de não junção. Essa correlação implícita é chamada de Contenção Simples.

Como alternativa, o Novo CE usa a Contenção de Base como correlação. O novo modelo CE pressupõe que os usuários podem consultar dados que não existem. Isso significa que os predicados de filtro em tabelas separadas podem não estar correlacionados entre si. Portanto, usamos uma abordagem probabilística.

Para muitos cenários práticos, usar a suposição de Contenção de Base cria estimativas melhores. Isso, por sua vez, cria opções de plano de consulta mais eficientes. No entanto, em algumas situações, usar a suposição de Contenção Simples pode fornecer melhores resultados. Se isso ocorrer, você poderá ter uma escolha de plano de consulta menos eficiente ao usar o Novo CE em vez do CE Herdado.

Para obter mais informações sobre como solucionar problemas relacionados ao Novo CE, consulte Diminuição do desempenho da consulta após a atualização do SQL Server 2012 ou anterior para 2014 ou posterior.

Solução

No SQL Server 2014 e versões posteriores, você pode usar o sinalizador de rastreamento 9476 para forçar o SQL Server a usar a suposição de Contenção Simples em vez da suposição de Contenção Base padrão. Se você puder modificar a consulta do aplicativo, a melhor opção é usar a dica ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS de consulta após o SQL Server 2016 (13.x) SP1. Para obter mais informações, consulte USE HINT. Por exemplo:

SELECT * FROM Table1 t1
JOIN Table2 t2
ON t1.Col1 = t2.Col1
WHERE Col1 = 10
OPTION (USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS'));

Habilitar esse sinalizador de rastreamento ou usar a dica de consulta pode melhorar a escolha do plano de consulta sem precisar reverter totalmente para o modelo CE herdado se as seguintes condições forem verdadeiras:

  • Você experimenta uma opção de plano de consulta abaixo do ideal que causa um desempenho geral degradado para consultas que contêm predicados de filtro de junções e não junção.
  • Você pode verificar uma imprecisão significativa em uma estimativa de "cardinalidade de junção" (ou seja, o número real versus estimado de linhas que diferem significativamente).
  • Essa imprecisão não existe quando você compila consultas usando o CE herdado.

Você pode habilitar esse sinalizador de rastreamento globalmente, no nível da sessão ou no nível da consulta.

Observação

O uso incorreto de sinalizadores de rastreamento pode prejudicar o desempenho da carga de trabalho. Para obter mais informações, veja Dicas (Transact-SQL) - Consulta.