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

Artigo
12/14/2024

Este artigo ajuda você a resolver o problema em que o alto uso da CPU ocorre quando você executa consultas no SQL Server.

Aplica-se a: SQL Server
Número original do KB: 2009160

Sintomas

Ao operar o Microsoft SQL Server que tem uma carga de trabalho altamente simultânea, você pode observar alguns problemas de desempenho nas consultas. Esse comportamento pode ser exibido como uso médio a alto da CPU ou solicitações extremas de concessão de memória.

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

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

Causa

Esse problema pode ocorrer em alguns casos em que o processador de consultas do SQL Server introduz uma operação de classificação opcional para melhorar o desempenho. Essa operação é conhecida como "Loop Aninhado Otimizado" ou "Classificação em Lote" e o otimizador de consulta determina quando introduzir melhor esses operadores. Em casos raros, a consulta toca apenas algumas linhas, mas o custo de configuraçã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.

Soluçã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 consultas a não usar uma operação de classificação (classificação em lote) para junções de loop aninhadas 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 completamente para garantir que 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 o código para usar a dica DISABLE_OPTIMIZED_NESTED_LOOP

Como alternativa, aplique a dica de consulta a seguir DISABLE_OPTIMIZED_NESTED_LOOP 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 Mecanismo de Banco de Dados

Aplica-se a

SQL Server 2005 até SQL Server 2019

Partilhar via