Le concessioni elevate di CPU o memoria possono verificarsi con query che utilizzano un ciclo annidato ottimizzato o un ordinamento batch

Questo articolo consente di risolvere il problema in cui si verifica un utilizzo elevato della CPU quando si eseguono query in SQL Server.

Si applica a: SQL Server
Numero KB originale: 2009160

Sintomi

Quando si gestisce Microsoft SQL Server con un carico di lavoro altamente simultaneo, è possibile che si verifichino alcuni problemi di prestazioni nelle query. Questo comportamento può presentare un utilizzo medio-elevato della CPU o richieste di concessione di memoria estreme.

È anche possibile che si verifichino altri effetti collaterali, ad esempio condizioni OOM, pressione della memoria per lo sfratto della cache del piano o attese impreviste RESOURCE_SEMAPHORE .

Inoltre, è possibile notare che i piani di query per le query che utilizzano una grande quantità di CPU o memorie hanno l'attributo OPTIMIZED per un operatore di join Nested Loops impostato su True.

Causa

Questo problema può verificarsi in alcuni casi in cui SQL Server query processor introduce un'operazione di ordinamento facoltativa per migliorare le prestazioni. Questa operazione è nota come "Ciclo annidato ottimizzato" o "Ordinamento batch" e Query Optimizer determina quando introdurre al meglio questi operatori. In rari casi, la query tocca solo alcune righe, ma il costo di installazione per l'operazione di ordinamento è così significativo che il costo del ciclo annidato ottimizzato supera i vantaggi. Pertanto, in questi casi è possibile osservare prestazioni più lente rispetto a quanto previsto.

Risoluzione

Flag di traccia 2340

Per risolvere il problema, usare il flag di traccia 2340 per disabilitare l'ottimizzazione. Il flag di traccia 2340 indica al processore di query di non usare un'operazione di ordinamento (ordinamento batch) per i join di cicli annidati ottimizzati durante la generazione di un piano di query. Ciò influisce sull'intera istanza.

Prima di abilitare questo flag di traccia, è possibile testare accuratamente le applicazioni per assicurarsi di ottenere i vantaggi previsti per le prestazioni quando si disabilita questa ottimizzazione. Ciò è dovuto al fatto che l'ottimizzazione dell'ordinamento può essere utile quando si verifica un aumento elevato del numero di righe toccate dal piano.

Per altre informazioni, vedere DBCC TRACEON - Flag di traccia (Transact-SQL).

Modificare il codice per usare l'hint DISABLE_OPTIMIZED_NESTED_LOOP

In alternativa, applicare l'hint di query seguente DISABLE_OPTIMIZED_NESTED_LOOP per disabilitare l'ottimizzazione a livello di query.

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

Per altre informazioni, vedere DISABLE_OPTIMIZED_NESTED_LOOP.

Ulteriori informazioni

Opzioni di avvio del servizio Motore di database

Si applica a

• SQL Server 2005-SQL Server 2019