Feedback 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 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. Le feedback d’allocation de mémoire tentent de mémoriser les besoins en mémoire d’une exécution antérieure (avec un feedback en mode centile, plusieurs exécutions passées). En fonction de ces informations de requête historiques, le feedback d’allocation de mémoire ajuste l’octroi donné à la requête en conséquence pour les exécutions suivantes.

Cette fonctionnalité a été lancée en trois vagues. Le feedback d’allocation de mémoire en mode batch, suivi du feedback d’allocation de mémoire en mode ligne, et SQL Server 2022 (16.x) ont introduit la persistance sur disque du feedback d’allocation de mémoire à l’aide du Magasin des requêtes, ainsi qu’un algorithme amélioré connu sous le nom d’allocation de centile.

Feedback d’allocation de mémoire en mode batch

S’applique à : SQL Server (à compter de SQL Server 2017 (14.x)), Azure SQL Database, Azure SQL Managed Instance (à compter du niveau de compatibilité de 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. Les allocations excessives qui entraînent une perte de mémoire et un accès concurrentiel réduit. Si l’allocation de mémoire est insuffisante, il en résulte des déversements coûteux sur le disque. En apportant une solution à la répétition des charges de travail, le feedback 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éversements coûteux sur le disque.

Le graphe suivant montre un exemple d’utilisation du feedback 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éversements 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 feedback 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éversements sont entièrement supprimés et l’allocation est plus importante :

Dimensionnement du feedback 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 feedback 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éversement sur le disque pour les opérateurs en mode batch, le feedback d’allocation de mémoire déclenche 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 s’afficher au moyen de l’événement étendu spilling_report_to_memory_grant_feedback. Cet événement renvoie l’ID de nœud du plan et la taille du déversement de données de ce nœud.

L’allocation de mémoire ajustée s’affiche dans le plan réel (post-exécution), au moyen de la propriété GrantedMemory.

Vous pouvez voir cette propriété dans l’opérateur racine du plan d’exécution de requêtes graphique ou dans la sortie 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" />

Afin 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;

Feedback 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 feedback d’allocation de mémoire se désactive sur une requête si la mémoire requise est instable. La fonctionnalité de feedback d’allocation de mémoire est désactivé après plusieurs exécutions répétées de la requête et ce comportement peut être observé en monitorant l’événement étendu memory_grant_feedback_loop_disabled. Cette condition est atténuée avec les modes persistance et centile pour le feedback d’allocation de mémoire introduits dans SQL Server 2022 (16.x). La fonctionnalité de persistance du feedback d’allocation de mémoire nécessite que le Magasin des requêtes soit activé dans la base de données et défini sur le mode « lecture-écriture ».

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

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

Le feedback 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 feedback d’allocation de mémoire. Cette fonctionnalité s’applique à l’exécution répétée d’instructions. Le feedback d’allocation de mémoire modifie uniquement le plan mis en cache. Avant SQL Server 2022 (16.x), les modifications n’étaient pas capturées dans le Magasin des requêtes.

Le feedback n’est pas persistant si le plan est supprimé du cache. Le feedback est également perdu en cas de basculement. Une instruction qui utilise OPTION (RECOMPILE) crée un plan et ne le met pas en cache. Comme il n’est pas mis en cache, aucun feedback 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 (autrement dit, avec le même hachage de requête) n’ayant pas utilisé OPTION (RECOMPILE) a été mise en cache, puis réexécutée, la deuxième exécution consécutive et les exécutions consécutives ultérieures peuvent bénéficier du feedback d’allocation de mémoire.

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

Vous pouvez suivre les événements de feedback d’allocation de mémoire à l’aide de l’événement étendu memory_grant_updated_by_feedback. 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 feedback 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 feedback d’allocation de mémoire a modifié le plan mis en cache.

Feedback 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 feedback d’allocation de mémoire en mode batch sans modification du niveau de compatibilité

Le feedback 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 le feedback 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 le feedback 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 le feedback 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 USE HINT indicateur de requête est prioritaire sur une configuration délimitée par la base de données ou un paramètre d’indicateur de trace.

Feedback d’allocation de mémoire en mode ligne

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

Le feedback d’allocation de mémoire en mode ligne étend la fonctionnalité de feedback 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 feedback d’allocation de mémoire en mode ligne dans Azure SQL Database, activez le niveau de compatibilité 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 batch, l’activité du feedback d’allocation de mémoire en mode ligne est visible au moyen de l’événement étendu memory_grant_updated_by_feedback. Nous introduisons également deux nouveaux attributs de plan d’exécution de requête pour une meilleure visibilité sur l’état actuel d’une opération de feedback d’allocation de mémoire pour les modes ligne et batch.

Le feedback d’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 Magasin des requêtes soit activé pour la base de données et qu’il soit dans un état de « lecture-écriture ». Pour en savoir plus sur la persistance, consultez Feedback d’allocation de mémoire en mode centile et persistance plus loin dans cet article.

L’activité de feedback d’allocation de mémoire en mode ligne est visible au moyen de l’événement étendu memory_grant_updated_by_feedback.

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 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.

Voici les valeurs s’affichant dans cet attribut :

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

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

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

• Le feedback d’allocation de mémoire en mode centile est activé par défaut dans SQL Server 2022 (16.x), mais n’a aucun effet si le Magasin des requêtes n’est pas activé ou dans un état « lecture-écriture ».
• Le mode persistance pour le feedback d’allocation de mémoire, CE et DOP est activé par défaut dans SQL Server 2022 (16.x), mais n’a aucun effet lorsque le Magasin des requêtes n’est pas activé ou dans un état « lecture-écriture ».
• Le mode centile et persistance pour le feedback d’allocation de mémoire est disponible dans Azure SQL Database et activé par défaut sur toutes les bases de données, existantes et nouvelles.
• Le mode centile et persistance pour le feedback d’allocation de mémoire n’est pas disponible actuellement 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 tirez l’avantage prévu de la fonctionnalité.

Le feedback d’allocation de mémoire est une fonctionnalité existante qui ajuste la taille de la mémoire allouée à 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 feedback 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 nouvelles 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 du feedback 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 charge de travail paramétrisée avait besoin de tailles d’allocation de mémoire très variables avec chaque exécution, les informations d’allocation les plus récentes pouvaient se révéler incorrectes. Elles risquaient de se trouver en décalage avec les besoins réels de la requête en cours d’exécution. Le feedback d’allocation de mémoire dans ce scénario n’est pas utile pour les performances, car nous ajustons toujours la mémoire en fonction de la dernière valeur d’allocation utilisée. L’image suivante montre le comportement possible avec le feedback d’allocation de mémoire sans mode centile et persistance.

Comme vous pouvez le constater, dans ce comportement de requête inhabituel mais possible, l’oscillation entre les quantités de mémoire réelles nécessaires et allouées entraîne une perte de mémoire et une mémoire insuffisante si l’exécution de la requête elle-même alterne en matière de quantité de mémoire. Dans ce scénario, le feedback d’allocation de mémoire se désactive, reconnaissant qu’il est contreproductif.

À l’aide d’un calcul basé sur les centiles 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’allocation en fonction de l’historique d’utilisation des exécutions passées et essayer d’optimiser la réduction des déversements. Par exemple, la même charge de travail alternée verrait le comportement d’allocation de mémoire suivant :

Un centile élevé des exigences de dimensionnement des allocations de mémoire passées pour les exécutions du plan mis en cache permet à l’optimiseur de requête de calculer les tailles d’allocation de mémoire, à l’aide de données persistantes dans le Magasin des requêtes. L’ajustement au 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 perte de mémoire.

Le mode persistance s’applique également au feedback DOP et au feedback CE.

Activer et désactiver les fonctionnalités de feedback d’allocation de mémoire

Désactiver le feedback d’allocation de mémoire en mode ligne sans modification du niveau de compatibilité

Le feedback 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 le feedback 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 feedback 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 le feedback 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 prévaut sur une configuration avec une étendue de base de données ou un paramètre d’indicateur de trace.

Activer le mode persistance et centile du feedback d’allocation de mémoire

Le feedback en mode persistance et centile est activé 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 un niveau supérieur pour la base de données à laquelle vous êtes connecté lors de l’exécution de la requête. Vous pouvez modifier ce paramètre au moyen d’ALTER DATABASE :

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

Le 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 mode centile

Pour désactiver le mode centile du feedback d’allocation de mémoire pour toutes les exécutions de requête en provenance 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.

Activer le mode persistance

Pour désactiver le mode persistance du feedback d’allocation de mémoire dans 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_PERSISTENCE = OFF;

La désactivation du mode persistance du feedback d’allocation de mémoire supprime également le feedback déjà collecté.

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

Considérations relatives au feedback d’allocation de mémoire

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

Étant donné que les données de feedback 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.

À compter de SQL Server 2022 (16.x), lorsque le Magasin des requêtes pour les réplicas secondaires est activé, le feedback d’allocation de mémoire prend en charge les réplicas secondaires dans les groupes de disponibilité. Le feedback d’allocation de mémoire peut appliquer le feedback différemment sur un réplica principal et sur un réplica secondaire. Toutefois, le feedback d’allocation de mémoire n’est pas persistant sur les réplicas secondaires. En outre, lors du basculement, le feedback d’allocation de mémoire de l’ancien réplica principal n’est pas appliqué au nouveau réplica principal. Tout feedback appliqué au réplica secondaire lorsqu’il devient le réplica principal est perdu. Pour en savoir plus, consultez Magasin des requêtes pour réplicas secondaires.

Contenu associé

• Traitement intelligent des requêtes dans les bases de données SQL
• Fonctionnalités de traitement intelligent des requêtes en détail
• RECONFIGURE (Transact-SQL)
• Monitorer et ajuster les performances
• MODIFIER LA CONFIGURATION DE L’ÉTENDUE DE LA BASE DE DONNÉES (Transact-SQL)