Des allocations élevées de processeur ou de mémoire peuvent se produire avec des requêtes qui utilisent une boucle imbriqué optimisée ou un tri par lots

Article
12/14/2024

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

S'applique à : SQL Server
Numéro de 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 remarquerez peut-être certains 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 les conditions OOM, la pression de la mémoire pour l’éviction du cache de plan ou des attentes inattendues RESOURCE_SEMAPHORE .

En outre, vous remarquerez peut-être que les plans de requête pour les requêtes qui consomment un grand nombre de processeurs ou de mémoires 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ù le processeur de requêtes SQL Server 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 présenter au mieux ces opérateurs. Dans de rares cas, la requête touche seulement 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 par rapport à 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 de l’instance.

Avant d’activer cet indicateur de trace, vous pouvez tester soigneusement vos applications pour vous assurer que vous bénéficiez des 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 lorsqu’il existe une augmentation importante du nombre de lignes qui sont 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

Partager via