Rétroaction d’allocation de mémoire

S’applique à : SQL Server 2017 (14.x) et versions ultérieures, Azure SQL Managed Instance, Azure SQL Database

Parfois, une requête s’exécute avec une allocation de mémoire trop grande ou trop petite. Si l’allocation de mémoire est trop volumineuse, nous inhibons le parallélisme sur le serveur. S’il est trop petit, nous pouvons être renversés sur le disque, ce qui est une opération coûteuse. Les commentaires sur l’octroi de mémoire tentent de mémoriser les besoins en mémoire d’une exécution antérieure (avec des commentaires centiles, plusieurs exécutions passées). En fonction de ces informations de requête historiques, les commentaires sur l’allocation de mémoire ajustent l’octroi donné à la requête en conséquence pour les exécutions suivantes.

Cette fonctionnalité a été publiée en trois vagues. Les commentaires sur l’octroi de mémoire en mode Batch, suivis des commentaires sur l’octroi de mémoire en mode ligne, et SQL Server 2022 (16.x) ont introduit des commentaires sur l’octroi de mémoire sur disque à l’aide du Magasin des requêtes, ainsi qu’un algorithme amélioré connu sous le nom d’allocation de centile.

Remarque

Pour obtenir d’autres fonctionnalités de commentaires de requête, consultez les commentaires d’estimation de cardinalité (CE) et les commentaires de degré de parallélisme (DOP).

Retour d’allocation de mémoire en mode batch

S’applique à : SQL Server (à partir de SQL Server 2017 (14.x)), Azure SQL Database

Le plan d’exécution d’une requête inclut la quantité minimale de mémoire nécessaire pour l’exécution et la taille d’allocation de mémoire idéale pour que toutes les lignes tiennent dans la mémoire. Les performances sont réduites quand les tailles d’allocation de mémoire ne sont pas dimensionnées correctement. Si l’allocation de mémoire est excessive, une certaine quantité de mémoire est inutilisée et l’accès concurrentiel est réduit. Si l’allocation de mémoire est insuffisante, il en résulte des dépassements de capacité coûteux sur le disque. En apportant une solution à la répétition des charges de travail, le retour d’allocation de mémoire en mode batch recalcule la quantité de mémoire réelle nécessaire pour une requête et met à jour la valeur d’allocation pour le plan mis en cache. Quand une instruction de requête identique est exécutée, la requête utilise la taille d’allocation de mémoire révisée, ce qui permet de réduire les allocations de mémoire excessives qui impactent l’accès concurrentiel et de corriger les allocations de mémoire sous-estimées qui provoquent des dépassements de capacité coûteux sur le disque.

Le graphe suivant montre un exemple d’utilisation du retour d’allocation de mémoire adaptative en mode batch. Pour la première exécution de la requête, la durée était de 88 secondes en raison de dépassements de capacité importants :

DECLARE @EndTime datetime = '2016-09-22 00:00:00.000';
DECLARE @StartTime datetime = '2016-09-15 00:00:00.000';

SELECT TOP 10 hash_unique_bigint_id
FROM dbo.TelemetryDS
WHERE Timestamp BETWEEN @StartTime AND @EndTime
GROUP BY hash_unique_bigint_id
ORDER BY MAX(max_elapsed_time_microsec) DESC;

Si le retour d’allocation de mémoire est activé, pour la deuxième exécution, la durée est de 1 seconde (au lieu de 88 secondes), les dépassements de capacité sont entièrement supprimés et l’allocation est plus importante :

Dimensionnement du retour d’allocation de mémoire

Dans le cas d’une allocation de mémoire excessive, si la mémoire allouée est plus de deux fois supérieure à la taille de la mémoire réelle utilisée, le retour d’allocation de mémoire recalcule l’allocation de mémoire et met à jour le plan mis en cache. Les plans dont les allocations de mémoire sont inférieures à 1 Mo ne sont pas recalculés en raison de dépassements.

Pour une condition d’allocation de mémoire insuffisante qui entraîne un déversement sur disque pour les opérateurs en mode batch, les commentaires sur l’octroi de mémoire déclenchent un recalcul de l’allocation de mémoire. Les événements de déversement sont signalés aux commentaires d’octroi de mémoire et peuvent être exposés via l’événement spilling_report_to_memory_grant_feedback étendu. Cet événement retourne l’ID de nœud du plan et la taille du dépassement de données de ce nœud.

L’allocation de mémoire ajustée apparaît dans le plan réel (après l’exécution) via la GrantedMemory propriété.

Vous pouvez voir cette propriété dans l’opérateur racine du plan d’affichage graphique ou dans la sortie XML du plan d’affichage :

<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="10336" RequiredMemory="1024" DesiredMemory="10336" RequestedMemory="10336" GrantWaitTime="0" GrantedMemory="10336" MaxUsedMemory="9920" MaxQueryMemory="725864" />

Pour que vos charges de travail soient automatiquement éligibles à cette amélioration, activez le niveau de compatibilité 140 pour la base de données.

Exemple :

ALTER DATABASE [WideWorldImportersDW] SET COMPATIBILITY_LEVEL = 140;

Retour d’allocation de mémoire et scénarios sensibles aux paramètres

Différentes valeurs de paramètre peuvent également nécessiter différents plans de requête afin de rester optimales. Ce type de requête est défini comme « sensible aux paramètres ».

Pour les plans sensibles aux paramètres, le retour d’allocation de mémoire se désactive sur une requête si la mémoire requise est instable. La fonctionnalité de commentaires sur l’allocation de mémoire est désactivée après plusieurs exécutions répétées de la requête et cela peut être observé en surveillant l’événement memory_grant_feedback_loop_disabled étendu. Cette condition est atténuée avec le mode de persistance et de centile pour les commentaires d’octroi de mémoire introduits dans SQL Server 2022 (16.x). La fonctionnalité de persistance des commentaires d’octroi de mémoire nécessite que les Magasin des requêtes soient activées dans la base de données et définies sur le mode « lecture écriture ».

Pour plus d’informations sur la détection de paramètres et la sensibilité des paramètres, consultez le Guide d’architecture du traitement des requêtes.

Mise en cache du retour d’allocation de mémoire

Le retour peut être stocké dans le plan mis en cache pour une seule exécution. Il s’agit toutefois des exécutions consécutives de cette instruction, qui bénéficient des ajustements de commentaires d’allocation de mémoire. Cette fonctionnalité s’applique à l’exécution répétée d’instructions. Le retour d’allocation de mémoire modifie uniquement le plan mis en cache. Avant SQL Server 2022 (16.x), les modifications n’ont pas été capturées dans le Magasin des requêtes.

Les commentaires ne sont pas conservés si le plan est supprimé du cache. Les commentaires seront également perdus en cas de basculement. Une instruction utilisant OPTION (RECOMPILE) crée un plan et ne la met pas en cache. Étant donné qu’il n’est pas mis en cache, aucun retour d’allocation de mémoire n’est généré et il n’est pas stocké pour cette compilation et cette exécution. Toutefois, si une instruction équivalente (autrement dit, avec le même hachage de requête) qui n’a pas été utilisée OPTION (RECOMPILE) a été mise en cache, puis réexécutée, la deuxième et les exécutions consécutives ultérieures peuvent tirer parti des commentaires d’octroi de mémoire.

Suivre l’activité de commentaires sur l’allocation de mémoire

Vous pouvez suivre les événements de commentaires d’octroi de mémoire à l’aide de l’événement memory_grant_updated_by_feedback étendu. Cet événement effectue le suivi de l’historique du nombre d’exécutions actuel, du nombre de fois que le plan a été mis à jour par le retour d’allocation de mémoire, de l’allocation de mémoire supplémentaire idéale avant modification et l’allocation de mémoire supplémentaire idéale après que le retour d’allocation de mémoire a modifié le plan mis en cache.

Retour d’allocation de mémoire, resource governor et indicateurs de requête

La mémoire réelle allouée respecte la limite de mémoire de requête déterminée par l’indicateur de requête ou resource governor.

Désactiver les commentaires d’octroi de mémoire en mode batch sans modifier le niveau de compatibilité

La rétroaction d’allocation de mémoire peut être désactivée dans l’étendue de la base de données ou de l’instruction tout en maintenant le niveau de compatibilité de base de données 140 et au-delà. Pour désactiver les commentaires d’octroi de mémoire en mode batch pour toutes les exécutions de requête provenant de la base de données, exécutez les instructions SQL ci-dessous dans le contexte de la base de données applicable :

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

-- Starting with SQL Server 2019, and in Azure SQL Database
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Quand il est activé, ce paramètre apparaît comme étant activé dans sys.database_scoped_configurations.

Pour réactiver les commentaires d’octroi de mémoire en mode batch pour toutes les exécutions de requête provenant de la base de données, exécutez les instructions SQL dans le contexte de la base de données applicable :

-- SQL Server 2017
ALTER DATABASE SCOPED CONFIGURATION SET DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK = OFF;

-- Azure SQL Database, SQL Server 2019 and higher
ALTER DATABASE SCOPED CONFIGURATION SET BATCH_MODE_MEMORY_GRANT_FEEDBACK = ON;

Vous pouvez aussi désactiver la rétroaction d’allocation de mémoire en mode batch pour une requête spécifique en désignant DISABLE_BATCH_MODE_MEMORY_GRANT_FEEDBACK en tant qu’indicateur de requête USE HINT. Par exemple :

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

Un indicateur de requête USE HINT est prioritaire sur un paramètre de configuration ou d’indicateur de trace dans l’étendue de la base de données.

Rétroaction d’allocation de mémoire en mode ligne

S’applique à : SQL Server (à partir de SQL Server 2019 (15.x)), Azure SQL Database

La rétroaction d’allocation de mémoire en mode ligne étend la fonctionnalité de rétroaction d’allocation de mémoire en mode batch en ajustant les tailles d’allocation de mémoire pour les opérateurs du mode batch et du mode ligne.

Pour activer les commentaires d’octroi de mémoire en mode ligne dans Azure SQL Database, activez le niveau de compatibilité de la base de données 150 ou supérieur pour la base de données à laquelle vous êtes connecté lors de l’exécution de la requête.

Exemple :

ALTER DATABASE [<database name>] SET COMPATIBILITY_LEVEL = 150;

Comme avec les commentaires d’octroi de mémoire en mode batch, l’activité de commentaires d’octroi de mémoire en mode ligne est visible via XEvent memory_grant_updated_by_feedback . Nous introduisons également deux nouveaux attributs de plan d’exécution de requête pour une meilleure visibilité de l’état actuel d’une opération de retour d’allocation de mémoire pour le mode ligne et batch.

Toutefois, Magasin des requêtes les améliorations de persistance introduites dans SQL Server 2022 (16.x) nécessitent que les Magasin des requêtes soient activées pour la base de données et dans un état « lecture écriture ». Pour plus d’informations sur la persistance, consultez les commentaires sur l’allocation de mémoire en mode centile et en mode de persistance plus loin dans cet article.

L’activité d’octroi de mémoire en mode ligne est visible via l’événement memory_grant_updated_by_feedback étendu.

Avec le feedback d’allocation de mémoire en mode ligne, deux nouveaux attributs de plan de requête apparaissent pour les plans réels après exécution : IsMemoryGrantFeedbackAdjusted et LastRequestedMemory, qui sont ajoutés à l’élément XML du plan de requête MemoryGrantInfo.

• L’attribut LastRequestedMemory affiche la mémoire accordée en Kilo-octets (Ko) de l’exécution de la requête précédente.
• L’attribut IsMemoryGrantFeedbackAdjusted vous permet de vérifier l’état du feedback d’allocation de mémoire pour l’instruction au sein d’un plan d’exécution de requête réel.

Les valeurs affichées dans cet attribut sont les suivantes :

IsMemoryGrantFeedbackAdjusted Valeur	Description
Non : première exécution	Les commentaires d’octroi de mémoire ne ajustent pas la mémoire pour la première compilation et l’exécution associée.
Non : allocation précise	S’il n’y a pas de déversement sur le disque et que l’instruction utilise au moins 50 % de la mémoire accordée, les commentaires d’allocation de mémoire ne sont pas déclenchés.
Non : rétroaction désactivée	Si les commentaires d’allocation de mémoire sont continuellement déclenchés et fluctuent entre l’augmentation de la mémoire et les opérations de diminution de la mémoire, le moteur de base de données désactive les commentaires d’octroi de mémoire pour l’instruction.
Oui : ajustement	Les commentaires sur l’allocation de mémoire ont été appliqués et peuvent être ajustés pour l’exécution suivante.
Oui : Ajustement du centile	Les commentaires sur l’octroi de mémoire sont appliqués à l’aide de l’algorithme d’octroi de centile, qui examine plus d’historique que seulement l’exécution la plus récente.
Oui : stable	La rétroaction d’allocation de mémoire a été appliquée et la mémoire allouée est maintenant stable ; en d’autres termes, ce qui a été alloué pour l’exécution précédente est identique à ce qui a été alloué pour l’exécution actuelle.

Rétroaction d’allocation de mémoire en mode centile et persistance

S’applique à : SQL Server (à partir de SQL Server 2022 (16.x)) et Azure SQL Database

Cette fonctionnalité a été introduite dans SQL Server 2022 (16.x), mais cette amélioration des performances est disponible pour les requêtes qui fonctionnent dans le niveau de compatibilité de la base de données 140 (introduit dans SQL Server 2017) ou une version ultérieure, ou l’indicateur QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n de 140 et versions ultérieures, et quand Magasin des requêtes est activé pour la base de données et est dans un état « lecture-écriture ».

• Les commentaires d’octroi de mémoire de centile sont activés par défaut dans SQL Server 2022 (16.x), mais n’ont aucun effet si Magasin des requêtes n’est pas activé ou si Magasin des requêtes n’est pas dans un état « lecture-écriture ».
• La persistance pour l’octroi de mémoire, ce et les commentaires DOP sont activés par défaut dans SQL Server 2022 (16.x), mais n’ont aucun effet lorsque Magasin des requêtes n’est pas activé ou lorsque Magasin des requêtes n’est pas dans un état « lecture-écriture ».
• Le centile et la persistance pour les commentaires sur l’octroi de mémoire sont disponibles dans Azure SQL Database et activés par défaut sur toutes les bases de données, existantes et nouvelles.
• Le centile et la persistance pour les commentaires sur l’octroi de mémoire ne sont pas actuellement disponibles dans Azure SQL Managed Instance.

Il est recommandé d’avoir une base de référence de performances pour votre charge de travail avant que la fonctionnalité soit activée pour votre base de données. Les numéros de base vous aideront à déterminer si vous obtenez l’avantage prévu de la fonctionnalité.

Le feedback d’allocation de mémoire (MGF) est une fonctionnalité existante qui ajuste la taille de la mémoire allouée pour une requête en fonction des performances passées. Dans les phases initiales de ce projet, toutefois, l’ajustement de l’allocation de mémoire avec le plan n’était stocké que dans le cache : si un plan était supprimé du cache, le processus de rétroaction devait recommencer. En résultaient de mauvaises performances la première fois qu’une requête était exécutée après la suppression. La nouvelle solution consiste à conserver les informations d’octroi avec les autres informations de requête dans le Magasin des requêtes afin que les avantages se terminent par les évictions du cache. La persistance et le centile répondent d’une manière non intrusive aux limitations existantes de la rétroaction d’allocation de mémoire.

En outre, les ajustements de taille des allocations ne tenaient compte que de la dernière allocation utilisée. Par conséquent, si une requête ou une charge de travail paramétrable nécessite des tailles d’allocation de mémoire considérablement variables avec chaque exécution, les informations d’octroi les plus récentes peuvent être inexactes. Il peut être obsolète des besoins réels de la requête en cours d’exécution. Les commentaires sur l’allocation de mémoire dans ce scénario ne sont pas utiles pour les performances, car nous ajustons toujours la mémoire en fonction de la dernière valeur d’octroi utilisée. L’image suivante montre le comportement possible avec les commentaires d’octroi de mémoire sans centile et mode de persistance.

Comme vous pouvez le voir, dans ce comportement de requête inhabituel mais possible, l’oscillation entre les quantités de mémoire réelles nécessaires et accordées entraîne un gaspillage et une mémoire insuffisante si l’exécution de la requête elle-même alterne en termes de quantité de mémoire. Dans ce scénario, les commentaires sur l’octroi de mémoire se désactivent, reconnaissant qu’il fait plus de mal que de bien.

À l’aide d’un calcul basé sur centile sur l’historique récent de la requête, au lieu de la dernière exécution, nous pouvons lisser les valeurs de taille d’octroi en fonction de l’historique d’utilisation de l’exécution passée et essayer d’optimiser la réduction des déversements. Par exemple, la même charge de travail alternée voit le comportement d’octroi de mémoire suivant :

L’optimiseur de requête utilise un centile élevé des exigences de dimensionnement des allocations de mémoire passées pour les exécutions du plan mis en cache pour calculer les tailles d’allocation de mémoire, à l’aide de données conservées dans le Magasin des requêtes. L’ajustement du centile, qui effectue les ajustements d’allocation de mémoire est basé sur l’historique récent des exécutions. Au fil du temps, l’allocation de mémoire donnée réduit les déversements et la mémoire perdue.

La persistance s’applique également aux commentaires DOP et aux commentaires CE.

Activer et désactiver les fonctionnalités de commentaires sur l’octroi de mémoire

Désactiver les commentaires d’allocation de mémoire en mode ligne sans modifier le niveau de compatibilité

Le retour d’allocation de mémoire en mode ligne peut être désactivé dans l’étendue de la base de données ou de l’instruction tout en maintenant le niveau de compatibilité de la base de données à au moins 150. Pour désactiver les commentaires d’octroi de mémoire en mode ligne pour toutes les exécutions de requête provenant de la base de données, exécutez les instructions SQL dans le contexte de la base de données applicable :

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = OFF;

Pour réactiver le retour d’allocation de mémoire en mode ligne pour toutes les exécutions de requêtes provenant de la base de données, exécutez ce qui suit dans le contexte de la base de données applicable :

ALTER DATABASE SCOPED CONFIGURATION SET ROW_MODE_MEMORY_GRANT_FEEDBACK = ON;

Vous pouvez aussi désactiver la rétroaction d’allocation de mémoire en mode ligne pour une requête spécifique en désignant DISABLE_ROW_MODE_MEMORY_GRANT_FEEDBACK en tant qu’indicateur de requête USE HINT. Par exemple :

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

Un indicateur de requête USE HINT est prioritaire sur un paramètre de configuration ou d’indicateur de trace dans l’étendue de la base de données.

Activer la persistance des commentaires d’octroi de mémoire et le centile

Les commentaires de persistance et de centile sont activés par défaut dans Azure SQL Database et SQL Server 2022 (16.x).

Utilisez le niveau de compatibilité de base de données 140 ou supérieur pour la base de données à laquelle vous êtes connecté lors de l’exécution de la requête. Vous pouvez le modifier via ALTER DATABASE :

ALTER DATABASE <DATABASE NAME> SET COMPATIBILITY LEVEL = 140; -- OR HIGHER

La Magasin des requêtes doit être activé pour chacune des bases de données sur lesquelles la partie persistance de cette fonctionnalité est utilisée.

Désactiver le centile

Pour désactiver le centile de commentaires d’octroi de mémoire pour toutes les exécutions de requête provenant de la base de données, exécutez ce qui suit dans le contexte de la base de données applicable :

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT = OFF;

Le paramètre par défaut de MEMORY_GRANT_FEEDBACK_PERCENTILE_GRANT est ON.

Désactiver la persistance

Pour désactiver la persistance de la rétroaction d’allocation de mémoire dans toutes les exécutions de requête provenant de la base de données,

exécutez les éléments suivants dans le contexte de la base de données concernée :

ALTER DATABASE SCOPED CONFIGURATION SET MEMORY_GRANT_FEEDBACK_PERSISTENCE = OFF;

La désactivation de la persistance de la rétroaction d’allocation de mémoire supprime également la rétroaction déjà collectée.

Le paramètre par défaut de MEMORY_GRANT_FEEDBACK_PERSISTENCE est ON.

Considérations relatives aux commentaires sur l’octroi de mémoire

Vous pouvez afficher vos paramètres actuels en interrogeant sys.database_scoped_configurations.

Remarque

Cette fonctionnalité ne fonctionnera pas si les deux BATCH_MODE_MEMORY_GRANT_FEEDBACK et ROW_MODE_MEMORY_GRANT_FEEDBACK sont définies sur OFF.

Étant donné que les données de commentaires sont désormais conservées dans le Magasin des requêtes, il existe une augmentation des exigences d’utilisation des Magasin des requêtes.

L’allocation de mémoire avec centile pèche par excès de réduction des déversements. Étant donné qu’elle n’est plus uniquement basée sur la dernière exécution, mais sur une observation de plusieurs exécutions passées, l’utilisation de la mémoire est susceptible d’augmenter pour les charges de travail oscillantes qui présentent une grande variation des exigences d’allocation de mémoire entre les exécutions.

À compter de SQL Server 2022 (16.x), lorsque Magasin des requêtes pour les réplicas secondaires est activé, les commentaires sur l’allocation de mémoire prennent en charge les réplicas pour les réplicas secondaires dans les groupes de disponibilité. Les commentaires sur l’octroi de mémoire peuvent appliquer des commentaires différemment sur un réplica principal et sur un réplica secondaire. Toutefois, les commentaires sur l’octroi de mémoire ne sont pas conservés sur les réplicas secondaires et sur le basculement, les commentaires sur l’allocation de mémoire de l’ancien réplica principal sont appliqués au nouveau réplica principal. Tous les commentaires appliqués au réplica secondaire lorsqu’il devient le réplica principal sont perdus. Pour plus d’informations, consultez Magasin des requêtes pour les réplicas secondaires.

Contenu connexe

• Traitement de requêtes intelligent dans les bases de données SQL
• Fonctionnalités de traitement des requêtes intelligentes en détail
• RECONFIGURE (Transact-SQL)
• Surveillance et réglage des performances
• ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)