Diminuição do desempenho da consulta após a atualização do SQL Server 2012 ou anterior para 2014 ou posterior

Depois de atualizar o SQL Server de 2012 ou de uma versão anterior para 2014 ou posterior, você poderá encontrar o seguinte problema: a maioria das consultas originais é executada bem, mas algumas de suas consultas são executadas mais lentamente do que na versão anterior. Embora existam muitas causas possíveis e fatores contribuintes, uma causa relativamente comum são as alterações no modelo de Estimativa de Cardinalidade (CE) após a atualização. Alterações significativas foram introduzidas nos modelos CE a partir do SQL Server 2014.

Este artigo fornece etapas de solução de problemas e resoluções para problemas de desempenho de consulta que ocorrem ao usar o CE padrão, mas não ocorrem ao usar o CE herdado.

Solução de problemas: Identifique se as alterações de CE são o problema e descubra o motivo

Etapa 1: Identificar se o CE padrão é usado

1. Escolha uma consulta que seja executada mais lentamente após a atualização.
2. Execute a consulta e colete o plano de execução.
3. Na janela Propriedades do plano de execução, marque CardinalityEstimationModelVersion.
4. Um valor de 70 indica o CE herdado e um valor de 120 ou superior indica o uso do CE padrão.

Se o CE herdado for usado, as alterações do CE não serão a causa do problema de desempenho. Se o CE padrão for usado, vá para a próxima etapa.

Etapa 2: Identificar se o Otimizador de Consulta pode gerar um plano melhor usando o CE herdado

Execute a consulta com o CE herdado. Se ele tiver um desempenho melhor do que usar o CE padrão, vá para a próxima etapa. Se o desempenho não melhorar, as alterações de CE não são a causa.

Etapa 3: Descobrir por que a consulta tem um desempenho melhor com o CE herdado

Teste as várias dicas de consulta relacionadas à CE para sua consulta. Para o SQL Server 2014, use os sinalizadores de rastreamento correspondentes 4137, 9472 e 4139 para testar a consulta. Determine quais dicas ou sinalizadores de rastreamento afetam positivamente o desempenho com base nesses testes.

Resolução

Para resolver o problema, tente usar um dos seguintes métodos:

• Otimize a consulta.

  Compreensivelmente, nem sempre é possível reescrever consultas, mas especialmente quando há apenas algumas consultas que podem ser reescritas, essa abordagem deve ser a primeira escolha. As consultas escritas de forma ideal têm melhor desempenho, independentemente das versões do CE.

• Use dicas de consulta identificadas na Etapa 3.

  Essa abordagem direcionada permite que outras cargas de trabalho se beneficiem das suposições e melhorias padrão do CE. Além disso, é uma opção mais robusta do que criar um guia de planos. E não requer QDS (Repositório de Consultas), ao contrário de forçar um plano (a opção mais robusta).

• Force um bom plano.

  Essa é uma opção favorável e pode ser usada para direcionar consultas específicas. Forçar um plano pode ser feito usando um guia de plano ou QDS. O QDS geralmente é mais fácil de usar.

• Use a configuração no escopo do banco de dados para forçar o CE herdado.

  Essa é uma abordagem menos preferencial, pois é uma configuração de todo o banco de dados e se aplica a todas as consultas nesse banco de dados. Ainda assim, às vezes é necessário quando uma abordagem direcionada não é viável. É certamente a opção mais fácil de implementar.

• Use o sinalizador de rastreamento 9841 para forçar o CE herdado globalmente. Para fazer isso, use DBCC TRACEON ou defina o sinalizador de rastreamento como um parâmetro de inicialização.

  Essa é a abordagem menos direcionada e só deve ser usada como uma mitigação temporária quando você não puder aplicar nenhuma das outras opções.

Opções para ativar o CE legado

Nível de consulta: use a opção Dica de consulta ou QUERYTRACEON

• Para SQL Server 2016 SP1 e versões posteriores, use hint FORCE_LEGACY_CARDINALITY_ESTIMATION para sua consulta, por exemplo:

  SELECT * FROM Table1
  WHERE Col1 = 10
  OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'));
  

• Habilite o sinalizador de rastreamento 9481 para forçar um plano CE herdado. Veja um exemplo:

  SELECT * FROM Table1
  WHERE Col1 = 10
  OPTION (QUERYTRACEON 9481)
  

Nível do banco de dados: definir a configuração com escopo ou o nível de compatibilidade

• Para SQL Server 2016 e versões posteriores, altere a configuração no escopo do banco de dados:

    --Force a specific database to use legacy CE
    ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
  
    -- Validate what databases use legacy CE
    SELECT name, value
        FROM sys.database_scoped_configurations 
    WHERE name = 'LEGACY_CARDINALITY_ESTIMATION';
  

• Altere o nível de compatibilidade do banco de dados. É a única opção de nível de banco de dados disponível para o SQL Server 2014. Observe que essa mudança afeta mais do que apenas o CE. Para determinar o impacto das alterações de nível de compatibilidade, acesse o nível de compatibilidade ALTER DATABASE (Transact-SQL) e examine as tabelas "Diferenças" nele.

  ALTER DATABASE <YourDatabase>
  SET COMPATIBILITY_LEVEL = 110  -- set it to SQL Server 2012 level
  

Nível do servidor: Usar sinalizador de rastreamento

Use o sinalizador de rastreamento 9481 para forçar o CE herdado em todo o servidor:

--Turn on 
DBCC TRACEON(9481, -1)
--Validate
DBCC TRACESTATUS

Perguntas frequentes

P1: Estou interessado em atualizar para uma versão mais recente do SQL Server e estou preocupado com as regressões de desempenho do avaliador de cardinalidade. Qual planejamento de atualização é recomendado para minimizar problemas?

Para bancos de dados pré-existentes em execução em níveis de compatibilidade mais baixos, o fluxo de trabalho recomendado para atualizar o processador de consulta para um nível de compatibilidade mais alto é detalhado em Alterar o modo de compatibilidade do banco de dados e usar os cenários de uso do repositório de consultas e do repositório de consultas. A metodologia apresentada no artigo se aplica a mudanças para 130 ou superior para SQL Server e Banco de Dados SQL do Azure.

Q2: Eu não tenho tempo para testar as mudanças de CE. O que posso fazer neste caso?

Para aplicativos e cargas de trabalho pré-existentes, não recomendamos mudar para o CE padrão até que testes de regressão suficientes tenham sido executados. Se você ainda tiver dúvidas, recomendamos que você ainda atualize o SQL Server e mude para o nível de compatibilidade mais recente disponível. Como precaução, habilite também o sinalizador de rastreamento 9481 para SQL Server 2014 ou defina a configuração ON no escopo do banco de dados LEGACY_CARDINALITY_ESTIMATION para SQL Server 2016 e versões posteriores até que você tenha a oportunidade de testar.

P3: Há alguma desvantagem em usar o CE legado permanentemente?

Melhorias e correções futuras relacionadas ao avaliador de cardinalidade são centradas em versões mais recentes. A versão 70 é um estado intermediário aceitável. No entanto, após testes cuidadosos, recomendamos eventualmente mudar para uma versão CE mais recente para se beneficiar das correções CE mais recentes. Há uma alta probabilidade de alterações no plano de consulta ao migrar do CE herdado, portanto, teste antes de fazer alterações nos sistemas de produção. As alterações podem melhorar o desempenho da consulta em muitos casos, mas, em alguns casos, o desempenho da consulta pode diminuir.

P4: Tenho milhares de bancos de dados e não quero ativar manualmente LEGACY_CARDINALITY_ESTIMATION para cada um. Existe um método alternativo?

Para o SQL Server 2014, habilite o sinalizador de rastreamento 9481 para usar o CE herdado para todos os bancos de dados, independentemente do nível de compatibilidade. Para SQL Server 2016 e versões posteriores, execute a consulta a seguir para iterar por meio de bancos de dados. A configuração será habilitada mesmo quando o banco de dados for restaurado ou anexado em outro servidor.

SELECT [name], 0 AS [isdone]
INTO #tmpDatabases
FROM master.sys.databases WITH (NOLOCK)
WHERE database_id > 4 AND source_database_id IS NULL AND is_read_only = 0

DECLARE @dbname sysname, @sqlcmd NVARCHAR(500);

WHILE (SELECT COUNT([name]) FROM #tmpDatabases WHERE isdone = 0) > 0
BEGIN
    SELECT TOP 1 @dbname = [name] FROM #tmpDatabases WHERE isdone = 0

    SET @sqlcmd = 'USE ' + QUOTENAME(@dbname) + '; 
        IF (SELECT [value] FROM sys.database_scoped_configurations WHERE [name] = ''LEGACY_CARDINALITY_ESTIMATION'') = 0
        ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;'
 
    BEGIN TRY
        EXECUTE sp_executesql @sqlcmd
    END TRY
    BEGIN CATCH
        SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState, ERROR_MESSAGE() AS ErrorMessage;
    END CATCH

    UPDATE #tmpDatabases
    SET isdone = 1
    WHERE [name] = @dbname
END;

Para o Banco de Dados SQL do Azure, você pode criar um tíquete de suporte para que esse sinalizador de rastreamento seja habilitado no nível da assinatura, mas não no nível do servidor.

P5: A execução com o CE herdado impedirá que eu tenha acesso a novos recursos?

Mesmo com o LEGACY_CARDINALITY_ESTIMATION habilitado, você ainda terá acesso à funcionalidade mais recente incluída na versão do SQL Server e no nível de compatibilidade do banco de dados associado. Por exemplo, um banco de dados com LEGACY_CARDINALITY_ESTIMATION habilitado em execução no nível de compatibilidade do banco de dados 140 no SQL Server 2017 ainda pode se beneficiar da família de recursos de processamento de consulta adaptável.

P6: Quando o CE legado deixará de ser suportado?

Não temos planos de parar de oferecer suporte ao CE legado neste momento. No entanto, futuras melhorias e correções relacionadas ao avaliador de cardinalidade estão centradas em versões mais recentes do CE.

P7: Tenho apenas algumas consultas regredindo com o CE padrão, mas o desempenho da maioria das consultas é o mesmo ou até melhorou. O que devo fazer?

Uma alternativa mais granular ao sinalizador de rastreamento no escopo do servidor 9481 ou à configuração no escopo do banco de dados LEGACY_CARDINALITY_ESTIMATION é o uso da construção USE HINT no escopo da consulta. Para obter mais informações, consulte Argumento de dica de consulta USE HINT no SQL Server 2016 e USE HINT.

USE HINT FORCE_LEGACY_CARDINALITY_ESTIMATION permite que você defina o modelo CE do otimizador de consulta para a versão 70, independentemente do nível de compatibilidade do banco de dados. Consulte Nível de consulta: use a opção Query Hint ou QUERYTRACEON.

Como alternativa, se houver apenas uma consulta problemática com o CE padrão, você poderá forçar um plano de CE herdado armazenado no Repositório de Consultas ou usar FORCE_LEGACY_CARDINALITY_ESTIMATION em conjunto com um guia de plano.

P8: Se o desempenho da consulta regredir devido a uma alteração de plano relacionada a superestimativas ou subestimativas significativas ao usar o CE padrão, o problema será corrigido no produto?

O EC é um problema complexo, e os algoritmos dependem dos dados menos que perfeitos disponíveis para estimativas, como estatísticas para tabelas e índices. Não há informações para algumas construções fora do modelo, como TVFs (funções com valor de tabela) e modelos baseados em muitas suposições (como correlação ou independência dos predicados e colunas, distribuição uniforme de dados, contenção e assim por diante).

Dadas as combinações ilimitadas de esquema, dados e cargas de trabalho do cliente, é quase impossível escolher modelos que funcionem para todos os casos. Embora algumas alterações no CE padrão possam conter bugs (como qualquer outro software) e possam ser corrigidas, outros problemas são causados por uma alteração de modelo.

As mudanças nas versões CE, especialmente passando de 70 para 120, incluem muitas opções diferentes para os modelos usados. Por exemplo, ao estimar filtros, assuma algum nível de correlação entre os predicados porque, na prática, essa correlação existe com frequência, e o modelo CE 70 subestimaria os resultados nesses casos. Embora essas alterações tenham sido testadas para muitas cargas de trabalho e melhorado muitas consultas, para algumas outras consultas, o CE herdado foi uma correspondência melhor e, portanto, com o CE padrão, regressões de desempenho podem ser observadas.

Infelizmente, não é considerado um bug. Nessas situações, use uma solução alternativa, como ajustar a consulta, assim como você precisava fazer com o CE herdado se o desempenho da consulta não for aceitável, ou forçar um modelo CE anterior ou um plano de execução específico.

P9: Há algum recurso para saber detalhes sobre as alterações de cardinalidade no CE padrão e o impacto no desempenho da consulta?

Consulte Otimizando seus planos de consulta com o Avaliador de Cardinalidade do SQL Server 2014 para obter detalhes e leia a seção "O que mudou no SQL Server 2014?".