Altas concessões de CPU ou memória podem ocorrer com consultas que usam loop aninhado otimizado ou classificação em lote

Este artigo ajuda você a resolve o problema em que o alto uso da CPU ocorre ao executar consultas em SQL Server.

Aplica-se ao: SQL Server
Número de KB original: 2009160

Sintomas

Quando você opera o Microsoft SQL Server que tem uma carga de trabalho altamente simultânea, você pode notar alguns problemas de desempenho em consultas. Esse comportamento pode exibir como uso médio a alto da CPU ou solicitações de concessão de memória extrema.

Você também pode experimentar outros efeitos colaterais, como condições de OOM, pressão de memória para despejo de cache de plano ou esperas inesperadas RESOURCE_SEMAPHORE .

Além disso, você pode observar que os planos de consulta para consultas que consomem muitas CPU ou memórias têm o atributo OPTIMIZED para um operador de junção de Loops Aninhados definido como True.

Motivo

Esse problema pode ocorrer em alguns casos em que SQL Server processador de consulta introduz uma operação de classificação opcional para melhorar o desempenho. Essa operação é conhecida como "Loop Aninhado Otimizado" ou "Classificação de Lote" e o otimizador de consulta determina quando apresentar melhor esses operadores. Em casos raros, a consulta toca apenas algumas linhas, mas o custo de instalação para a operação de classificação é tão significativo que o custo do loop aninhado otimizado supera seus benefícios. Portanto, nesses casos, você pode observar um desempenho mais lento em comparação com o esperado.

Resolução

Sinalizador de rastreamento 2340

Para corrigir o problema, use o sinalizador de rastreamento 2340 para desabilitar a otimização. O sinalizador de rastreamento 2340 instrui o processador de consulta a não usar uma operação de classificação (classificação em lote) para junções de loop aninhados otimizadas ao gerar um plano de consulta. Isso afeta toda a instância.

Antes de habilitar esse sinalizador de rastreamento, você pode testar seus aplicativos minuciosamente para garantir que você obtenha os benefícios de desempenho esperados ao desabilitar essa otimização. Isso ocorre porque a otimização de classificação pode ser útil quando há um grande aumento no número de linhas que são tocadas pelo plano.

Para obter mais informações, consulte DBCC TRACEON – Sinalizadores de rastreamento (Transact-SQL).

Modificar código para usar a dica DISABLE_OPTIMIZED_NESTED_LOOP

Como alternativa, aplique a seguinte DISABLE_OPTIMIZED_NESTED_LOOP dica de consulta para desabilitar a otimização no nível da consulta.

SELECT * FROM Person.Address  
WHERE City = 'SEATTLE' AND PostalCode = 98104
OPTION (USE HINT (DISABLE_OPTIMIZED_NESTED_LOOP)); 

Para obter mais informações, consulte DISABLE_OPTIMIZED_NESTED_LOOP.

Mais informações

Opções de Inicialização do Serviço do Mecanismo de Banco de Dados

Aplicável a

• SQL Server 2005 a SQL Server 2019