Lier une base de données avec des tables à mémoire optimisée à un pool de ressources

Article
08/23/2023

S'applique à : SQL Server

Un pool de ressources représente un sous-ensemble de ressources physiques qui peuvent être régies. Par défaut, les bases de données de SQL Server sont liées aux ressources du pool de ressources par défaut et consomment ces dernières. Pour empêcher que toutes les ressources de SQL Server soient consommées par une ou plusieurs tables mémoire optimisées, et que d'autres utilisateurs consomment la mémoire requise par les tables mémoire optimisées, nous vous recommandons de créer un pool de ressources distinct afin de gérer la consommation de mémoire pour la base de données avec des tables mémoire optimisées.

Une base de données ne peut être liée qu'à un seul pool de ressources. Toutefois, vous pouvez lier plusieurs bases de données au même pool. SQL Server permet de lier une base de données sans tables optimisées en mémoire à un pool de ressources, mais cela n’a aucun effet. Vous pouvez lier une base de données à un pool de ressources nommé, si, à l'avenir, vous souhaitez créer des tables mémoire optimisées dans la base de données.

Vous ne pouvez lier une base de données à un pool de ressources que si cette base de données et ce pool de ressources ont été créés au préalable. La liaison prendra effet lors de la prochaine mise en ligne (ONLINE) de la base de données. Pour plus d’informations, consultez États d’une base de données .

Pour plus d’informations sur les pools de ressources, consultez Pool de ressources de Resource Governor.

Étapes pour lier une base de données à un pool de ressources

Autre contenu de cette rubrique

Créer la base de données et le pool de ressources

Vous pouvez créer la base de données et le pool de ressources dans n'importe quel ordre. L'important est de créer les deux avant de procéder à leur liaison.

Création de la base de données

Le code Transact-SQL suivant crée la base de données IMOLTP_DB destinée à contenir une ou plusieurs tables mémoire optimisées. Le chemin <driveAndPath> doit exister avant d’exécuter cette commande.

CREATE DATABASE IMOLTP_DB  
GO  
ALTER DATABASE IMOLTP_DB ADD FILEGROUP IMOLTP_DB_fg CONTAINS MEMORY_OPTIMIZED_DATA  
ALTER DATABASE IMOLTP_DB ADD FILE( NAME = 'IMOLTP_DB_fg' , FILENAME = 'c:\data\IMOLTP_DB_fg') TO FILEGROUP IMOLTP_DB_fg;  
GO

Déterminer la valeur minimale pour MIN_MEMORY_PERCENT et MAX_MEMORY_PERCENT

Après avoir déterminé les besoins en mémoire des tables mémoire optimisées, vous devez déterminer le pourcentage de mémoire disponible dont vous avez besoin, et définir les pourcentages de mémoire sur cette valeur ou sur une valeur supérieure.

Exemple :
Pour cet exemple, nous allons supposer que vos calculs ont déterminé que les tables et les index mémoire optimisés ont besoin de 16 Go de mémoire. Supposons que vous disposez de 32 Go de mémoire allouée.

À première vue, il semblerait que vous deviez définir MIN_MEMORY_PERCENT et MAX_MEMORY_PERCENT à 50 (16 est 50 % de 32). Cependant, cela ne permettrait pas de fournir à vos tables mémoire optimisées suffisamment de mémoire. En observant la table ci-dessous (Pourcentage de mémoire disponible pour les tables et index mémoire optimisés), nous voyons que si vous disposez de 32 Go de mémoire allouée, seulement 80 % est disponible pour les tables et les index optimisés en mémoire. Par conséquent, il convient de calculer les pourcentages minimum et maximum en fonction de la mémoire, et non de la mémoire allouée.

memoryNeedeed = 16
memoryCommitted = 32
availablePercent = 0.8
memoryAvailable = memoryCommitted * availablePercent
percentNeeded = memoryNeeded / memoryAvailable

Chiffres réels :
percentNeeded = 16 / (32 * 0.8) = 16 / 25.6 = 0.625

Ainsi, vous avez besoin au moins de 62,5 % de la mémoire disponible pour obtenir les 16 Go requis pour vos tables et index mémoire optimisés. Étant donné que les valeurs de MIN_MEMORY_PERCENT et MAX_MEMORY_PERCENT doivent être des entiers, nous devons les définir sur au moins 63 %.

Créer un pool de ressources et configurer la mémoire

Lors de la configuration de tables mémoire optimisées, la planification des capacités doit être effectuée sur MIN_MEMORY_PERCENT, et non sur MAX_MEMORY_PERCENT. Consultez ALTER RESOURCE POOL (Transact-SQL) pour des informations sur MIN_MEMORY_PERCENT et MAX_MEMORY_PERCENT. Ce paramètre fournit une disponibilité de mémoire plus prédictible pour les tables mémoire optimisées, car MIN_MEMORY_PERCENT sollicite la mémoire d'autres pools de ressources pour s'assurer qu'il est servi. Pour garantir que la mémoire est disponible et éviter les conditions OOM (mémoire insuffisante), les valeurs de MIN_MEMORY_PERCENT et MAX_MEMORY_PERCENT doivent être identiques. Consultez Pourcentage de mémoire disponible pour les tables et index mémoire optimisés ci-dessous pour obtenir le pourcentage de la mémoire disponible pour les tables optimisées en mémoire en fonction de la quantité de mémoire allouée.

Pour plus d’informations sur le fonctionnement dans un environnement de machines virtuelles, consultez Meilleures pratiques : utilisation de l’OLTP en mémoire dans un environnement de machine virtuelle .

Le code Transact-SQL suivant crée un pool de ressources nommé Pool_IMOLTP avec la moitié de la mémoire disponible. Une fois le pool créé, Resource Governor est reconfiguré afin d'inclure Pool_IMOLTP.

-- set MIN_MEMORY_PERCENT and MAX_MEMORY_PERCENT to the same value  
CREATE RESOURCE POOL Pool_IMOLTP   
  WITH   
    ( MIN_MEMORY_PERCENT = 63,   
    MAX_MEMORY_PERCENT = 63 );  
GO  
  
ALTER RESOURCE GOVERNOR RECONFIGURE;  
GO

Lier la base de données au pool

Utilisez la fonction système sp_xtp_bind_db_resource_pool pour lier la base de données au pool de ressources. La fonction accepte deux paramètres : le nom de la base de données et le nom du pool de ressources.

Le code Transact-SQL suivant définit une liaison de la base de données IMOLTP_DB au pool de ressources Pool_IMOLTP. La liaison ne devient effective que lorsque vous mettez la base de données en ligne.

EXEC sp_xtp_bind_db_resource_pool 'IMOLTP_DB', 'Pool_IMOLTP'  
GO

La fonction système sp_xtp_bind_db_resource_pool accepte deux paramètres de chaîne : database_name et pool_name.

Confirmer la liaison

Confirmez la liaison, en prenant soin de noter l'ID du pool de ressources pour IMOLTP_DB. Cette valeur ne doit pas être NULL.

SELECT d.database_id, d.name, d.resource_pool_id  
FROM sys.databases d  
GO

Rendre la liaison effective

Vous devez mettre la base de données hors ligne, puis en ligne, après sa liaison au pool de ressources afin que la liaison prenne effet. Si votre base de données était déjà liée à un autre pool de ressources, cela supprime la mémoire allouée du pool précédent afin que les allocations de mémoire pour vos index et votre table mémoire optimisée émanent désormais du nouveau pool de ressources lié à la base de données.

USE master  
GO  
  
ALTER DATABASE IMOLTP_DB SET OFFLINE  
GO  
ALTER DATABASE IMOLTP_DB SET ONLINE  
GO  
  
USE IMOLTP_DB  
GO

Maintenant, la base de données est liée au pool de ressources.

Modifier MIN_MEMORY_PERCENT et MAX_MEMORY_PERCENT sur un pool existant

Si vous ajoutez de la mémoire supplémentaire au serveur ou si la quantité de mémoire requise pour vos tables mémoire optimisées change, il peut être nécessaire de remplacer la valeur de MIN_MEMORY_PERCENT et de MAX_MEMORY_PERCENT. Les étapes suivantes vous montrent comment modifier la valeur de MIN_MEMORY_PERCENT et de MAX_MEMORY_PERCENT sur un pool de ressources. Consultez la section ci-dessous, pour des conseils sur les valeurs à utiliser pour MIN_MEMORY_PERCENT et MAX_MEMORY_PERCENT. Consultez la rubrique Meilleures pratiques : utilisation de l’OLTP en mémoire dans un environnement de machine virtuelle pour plus d’informations.

Utilisez ALTER RESOURCE POOL pour modifier à la fois la valeur de MIN_MEMORY_PERCENT et de MAX_MEMORY_PERCENT.
Utilisez ALTER RESOURCE GOVERNOR pour reconfigurer Resource Governor avec les nouvelles valeurs.

Exemple de code

ALTER RESOURCE POOL Pool_IMOLTP  
WITH  
     ( MIN_MEMORY_PERCENT = 70,  
       MAX_MEMORY_PERCENT = 70 )   
GO  
  
-- reconfigure the Resource Governor  
ALTER RESOURCE GOVERNOR RECONFIGURE  
GO

Pourcentage de mémoire disponible pour les tables et index mémoire optimisés

Si vous mappez une base de données avec des tables mémoire optimisées et une charge de travail SQL Server au même pool de ressources, Resource Governor définit un seuil interne pour l'utilisation de l'OLTP en mémoire afin que les utilisateurs du pool ne rencontrent aucun conflit au niveau de l'utilisation du pool. D'une manière générale, le seuil d'utilisation de l'OLTP en mémoire représente environ 80 % du pool. Le tableau suivant montre les seuils réels pour différentes capacités de mémoire.

Lorsque vous créez un pool de ressources dédié pour la base de données de l’OLTP en mémoire, vous devez estimer la quantité de mémoire physique dont vous avez besoin pour les tables en mémoire compte tenu de la croissance des versions de ligne et des données. Après avoir estimé la mémoire nécessaire, créez un pool de ressources avec un pourcentage de la mémoire cible allouée à l’instance SQL comme indiqué par la colonne « committed_target_kb » dans la DMV sys.dm_os_sys_info. Par exemple, créez un pool de ressources P1 avec 40 % de la mémoire totale disponible sur l'instance. Parmi ces 40 %, le moteur de l’OLTP en mémoire obtient un pourcentage plus petit pour enregistrer les données de l’OLTP en mémoire. Cela a pour but de s'assurer que l’OLTP en mémoire ne consomme pas toute la mémoire de ce pool. Cette valeur de pourcentage inférieur dépend de la mémoire allouée cible. Le tableau suivant décrit la mémoire disponible pour la base de données de l’OLTP en mémoire dans un pool de ressources (nommé ou par défaut) avant qu'une situation d'insuffisance de mémoire soit déclenchée.

Mémoire validée cible	Pourcentage disponible pour les tables en mémoire
<= 8 Go	70 %
<= 16 Go	75 %
<= 32 Go	80 %
<= 96 Go	85 %
>96 Go	90 %

Par exemple, si votre « mémoire allouée cible » est de 100 Go et vous estimez que les tables et les index à mémoire optimisée ont besoin de 60 Go de mémoire, créez un pool de ressources avec MAX_MEMORY_PERCENT = 67 (60 Go nécessaires/0,90 = 66,667 Go – arrondi à 67 Go ; 67 Go/100 Go installés = 67 %) pour vous assurer que vos objets OLTP en mémoire disposent des 60 Go dont ils ont besoin.

Une fois qu'une base de données a été liée à un pool de ressources nommé, utilisez la requête suivante pour afficher les allocations de mémoire sur les différents pools de ressources.

SELECT pool_id  
     , Name  
     , min_memory_percent  
     , max_memory_percent  
     , max_memory_kb/1024 AS max_memory_mb  
     , used_memory_kb/1024 AS used_memory_mb   
     , target_memory_kb/1024 AS target_memory_mb  
   FROM sys.dm_resource_governor_resource_pools

Les résultats indiquent que la mémoire consommée par les objets mémoire optimisés est de 1356 Mo dans le pool de ressources, PoolIMOLTP, avec une limite supérieure de 2307 Mo. Cette limite supérieure contrôle la mémoire totale pouvant être consommée par les objets mémoire optimisés système et utilisateur mappés à ce pool.

Exemple de sortie
Ce résultat concerne la base de données et les tables créées précédemment.

pool_id     Name        min_memory_percent max_memory_percent max_memory_mb used_memory_mb target_memory_mb  
----------- ----------- ------------------ ------------------ ------------- -------------- ----------------   
1           internal    0                  100                3845          125            3845  
2           default     0                  100                3845          32             3845  
259         Pool_IMOLTP 0                  100                3845          1356           2307

Pour plus d’informations, consultez sys.dm_resource_governor_resource_pools (Transact-SQL).

Si vous ne liez pas votre base de données à un pool de ressources nommé, elle est liée au pool « par défaut ». Étant donné que le pool de ressources par défaut est utilisé par SQL Server pour la plupart des autres allocations, vous ne pourrez pas surveiller la mémoire consommée par les tables mémoire optimisées à l'aide de la DMV sys.dm_resource_governor_resource_pools précisément pour la base de données.

Voir aussi

sys.sp_xtp_bind_db_resource_pool (Transact-SQL)
sys.sp_xtp_unbind_db_resource_pool (Transact-SQL)
gouverneur de ressources
Pool de ressources de Resource Governor
Créer un pool de ressources
Modifier les paramètres de pool de ressources
Supprimer un pool de ressources

Partage via