Une utilisation du processeur ou des allocations de mémoire élevées peuvent survenir avec des requêtes qui utilisent une boucle imbriquée optimisée ou un tri par lots

Cet article vous aide à résoudre le problème d’utilisation élevée du processeur lorsque vous exécutez des requêtes dans SQL Server.

S’applique à : SQL Server
Numéro de la base de connaissances d’origine : 2009160

Symptômes

Lorsque vous utilisez Microsoft SQL Server qui a une charge de travail très simultanée, vous pouvez remarquer des problèmes de performances dans les requêtes. Ce comportement peut présenter une utilisation moyenne à élevée du processeur ou des demandes d’allocation de mémoire extrêmes.

Vous pouvez également rencontrer d’autres effets secondaires, tels que des conditions OOM, une sollicitation de la mémoire pour l’éviction du cache du plan ou des attentes inattendues RESOURCE_SEMAPHORE .

En outre, vous pouvez remarquer que les plans de requête pour les requêtes qui consomment beaucoup de processeur ou de mémoire ont l’attribut OPTIMIZED pour un opérateur de jointure de boucles imbriquées défini sur True.

Cause

Ce problème peut se produire dans certains cas où SQL Server processeur de requêtes introduit une opération de tri facultative pour améliorer les performances. Cette opération est appelée « boucle imbriquée optimisée » ou « tri par lots » et l’optimiseur de requête détermine quand introduire au mieux ces opérateurs. Dans de rares cas, la requête ne touche que quelques lignes, mais le coût d’installation de l’opération de tri est si important que le coût de la boucle imbriquée optimisée l’emporte sur ses avantages. Par conséquent, dans ces cas, vous pouvez observer des performances plus lentes que ce qui est attendu.

Résolution

Indicateur de trace 2340

Pour résoudre le problème, utilisez l’indicateur de trace 2340 pour désactiver l’optimisation. L’indicateur de trace 2340 indique au processeur de requêtes de ne pas utiliser d’opération de tri (tri par lots) pour les jointures de boucle imbriquées optimisées lors de la génération d’un plan de requête. Cela affecte l’ensemble du instance.

Avant d’activer cet indicateur de trace, vous pouvez tester minutieusement vos applications pour vous assurer que vous obtenez les avantages attendus en matière de performances lorsque vous désactivez cette optimisation. Cela est dû au fait que l’optimisation du tri peut être utile en cas d’augmentation importante du nombre de lignes touchées par le plan.

Pour plus d’informations, consultez DBCC TRACEON - Indicateurs de trace (Transact-SQL).

Modifier le code pour utiliser l’indicateur de DISABLE_OPTIMIZED_NESTED_LOOP

Vous pouvez également appliquer l’indicateur de requête suivant DISABLE_OPTIMIZED_NESTED_LOOP pour désactiver l’optimisation au niveau de la requête.

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

Pour plus d’informations, consultez DISABLE_OPTIMIZED_NESTED_LOOP.

Plus d’informations

Options de démarrage du service moteur de base de données

S’applique à

• SQL Server 2005 à SQL Server 2019