Rétroaction d’allocation de mémoire

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

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 grande, nous inhibons le parallélisme sur le serveur. Si elle est trop petite, nous pouvons déverser sur le disque, ce qui est une opération coûteuse. Les retours sur l’allocation de mémoire tentent de mémoriser les besoins en mémoire d’une exécution antérieure (avec des retours centiles, plusieurs exécutions passées). En fonction de ces informations de requête historiques, les retours 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 retours sur l’allocation de mémoire en mode Batch, suivis des retours sur l’allocation de mémoire en mode ligne, et SQL Server 2022 (16.x) ont introduit des retours sur l’allocation 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 d'autres caractéristiques du retour sur les requêtes, consultez Retour sur l'estimation de la cardinalité (CE) et Retour d'information sur le 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)), base de données Azure SQL, Azure SQL Managed Instance (à partir du niveau de compatibilité de la base de données 140)

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.

Dans le cas d’une allocation de mémoire dont la taille est insuffisante et qui entraîne un dépassement de capacité sur le disque pour les opérateurs en mode batch, le retour d’allocation de mémoire déclenchera un nouveau calcul de l’allocation de mémoire. Les événements de déversement sont signalés au feedback d’allocation de mémoire et peuvent être exposés via spilling_report_to_memory_grant_feedback l’événement é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 (post-exécution), via la GrantedMemory propriété.

Vous pouvez voir cette propriété dans l'opérateur racine du plan d'exécution de requêtes graphique ou dans le résultat XML du plan d'exécution de requêtes :

<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 pour maintenir une situation optimale. 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 fonction de retour sur l’allocation de mémoire est désactivée après plusieurs exécutions répétées de la requête, ce qui peut être observé en surveillant memory_grant_feedback_loop_disabled l'événement étendu. Cette condition est atténuée avec le mode de persistance et de centile pour les retours d’allocation de mémoire introduits dans SQL Server 2022 (16.x). La fonctionnalité de persistance des retours d’allocation 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 le reniflage de paramètres et la sensibilité des paramètres, consultez le Guide de l'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. Toutefois, ce sont les exécutions consécutives de cette instruction qui bénéficient des ajustements du retour 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.

Le retour n’est pas persistant si le plan est supprimé du cache. Le retour est également perdu en cas de basculement. Une instruction qui utilise OPTION (RECOMPILE) crée un plan et ne le met pas en cache. Parce 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 l’exécution. Toutefois, si une instruction équivalente (c'est-à-dire avec le même hachage de requête) n'utilisant pasOPTION (RECOMPILE) est mise en cache puis réexécutée, la deuxième exécution et les exécutions consécutives ultérieures peuvent bénéficier d'un retour d'information sur l'allocation de mémoire.

Suivre l’activité du retour d’allocation de mémoire

Vous pouvez suivre les événements de retours d’allocation de mémoire à l’aide de memory_grant_updated_by_feedback l’événement é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 le retour d’allocation de mémoire en mode batch sans modification du 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 la rétroaction d’allocation de mémoire en mode batch pour toutes les exécutions de requête en provenance 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 la rétroaction d’allocation de mémoire en mode batch pour toutes les exécutions de requête en provenance 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 par rapport à une configuration incluse dans l’étendue d’une base de données ou à un paramètre d’indicateur de trace.

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

S’applique à : SQL Server (à partir de SQL Server 2019 (15.x)), base de données Azure SQL, Azure SQL Managed Instance (à partir du niveau de compatibilité de la base de données 150)

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 le retour sur l’allocation 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 le feedback d’allocation de mémoire en mode par lots, l’activité du feedback d’allocation de mémoire en mode ligne est visible via le memory_grant_updated_by_feedback XEvent. 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.

Le retour sur l'allocation de mémoire ne nécessite pas le Magasin des requêtes, mais les améliorations de la persistance introduites dans SQL Server 2022 (16.x) exigent que le Query Store soit activé pour la base de données et qu'il soit dans un état de « lecture-écriture ». Pour plus d’informations sur la persistance, consultez les retours sur l’allocation de mémoire en mode centile et en mode de persistance plus loin dans cet article.

L’activité de retour d’allocation de mémoire en mode ligne est visible via memory_grant_updated_by_feedback l’événement é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 montre la mémoire allouée en kilo-octets (Ko) lors de l’exécution précédente de la requête.
• 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 :

Valeur IsMemoryGrantFeedbackAdjusted	Description
Non : première exécution	La rétroaction d’allocation de mémoire n’ajuste 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épassement sur disque et que l’instruction utilise au moins 50 % de la mémoire allouée, la rétroaction d’allocation de mémoire n’est pas déclenchée.
Non : rétroaction désactivée	Si la rétroaction d’allocation de mémoire est déclenchée en permanence et varie entre des opérations d’augmentation et de diminution de la mémoire, le moteur de base de données désactivera pour l’instruction.
Oui : ajustement	La rétroaction d’allocation de mémoire a été appliquée et pourrait être ajustée pour l’exécution suivante.
Oui : ajustement du centile	Les retours sur l’allocation 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)), base de données Azure SQL, Azure SQL Managed Instance (actuellement, persistance uniquement)

Cette caractéristique a été introduite dans SQL Server 2022 (16.x), cependant, 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 postérieur, ou QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n l’indicateur de 140 et postérieur, et quand le magasin des requêtes est activé pour la base de données et est dans un état « lecture écriture ».

• Les retours d’allocation 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’allocation 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 retours sur l’allocation de mémoire sont disponibles dans la base de données Azure SQL et activés par défaut sur toutes les bases de données, existantes et nouvelles.
• Le centile et la persistance pour les retours sur l’allocation de mémoire ne sont pas actuellement disponibles dans Azure SQL Managed Instance.

Nous vous recommandons de mettre en place une base de référence de performances pour votre charge de travail avant d’activer la fonctionnalité pour votre base de données. Les chiffres de référence vous permettront de déterminer si vous retirez l’avantage prévu de la fonctionnalité.

Le retour d'expérience sur l'allocation de mémoire (MGF) est une fonctionnalité existante qui ajuste la taille de la mémoire allouée à une requête sur la base 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 à rendre persistantes les informations d’allocation avec les autres informations sur la requête dans le Magasin des requêtes. Ainsi, les avantages perdurent d’une suppression du cache à l’autre. 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. Or, si une requête ou une charge de travail paramétrable avait besoin de tailles d’allocation de mémoire très variables à chaque exécution, les dernières informations d’allocation pouvaient se révéler incorrectes. Il pourrait être en décalage avec les besoins réels de la requête exécutée. Les retours 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 retours d’allocation 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 cas, le retour de l'allocation de mémoire se désactive d'elle-même, reconnaissant qu'elle 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’allocation 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 par centile, qui permet d'effectuer les ajustements de l'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 retours DOP et aux retours CE.

Activer et désactiver les fonctionnalités de retours sur l’allocation de mémoire

Désactiver le retour d’allocation de mémoire en mode ligne sans modification du 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 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 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 par rapport à une configuration incluse dans l’étendue d’une base de données ou à un paramètre d’indicateur de trace.

Activer la persistance du retour sur l'allocation de mémoire et le centile

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

Utilisez le niveau de compatibilité de la 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 du retour d'information sur l'allocation de mémoire pour toutes les exécutions de requêtes provenant de la base de données, exécutez la procédure suivante dans le contexte de la base de données concernée :

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 retours sur l’allocation de mémoire

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

Remarque

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

Étant donné que les données de rétroaction sont désormais persistantes dans le Magasin des requêtes, on observe une augmentation de ses exigences d’utilisation.

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.

À partir de SQL Server 2022 (16.x), lorsque le Magasin des requêtes pour les réplicas secondaires est activé, le retour d’expérience sur l’allocation de mémoire prend en charge les réplicas pour les réplicas secondaires dans les groupes de disponibilité. Le retour d’expérience sur l’allocation de mémoire peut appliquer le retour d’expérience différemment sur un réplica principal et sur un réplica secondaire. Toutefois, le retour d’expérience sur l’allocation de mémoire n’est pas conservé sur les réplicas secondaires et sur le basculement le retour d’expérience sur l’allocation de mémoire de l’ancien réplica principal n’est pas appliqué au nouveau réplica principal. Tous les retours 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 réplicas secondaires.

Contenu connexe

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