Lier une base de données avec des tables Memory-Optimized à un pool de ressources

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 SQL Server sont liées et consomment les ressources du pool de ressources par défaut. Pour protéger SQL Server d’avoir ses ressources consommées par une ou plusieurs tables optimisées en mémoire et pour empêcher d’autres utilisateurs de mémoire de consommer de la mémoire nécessaire par des tables optimisées en mémoire, vous devez créer un pool de ressources distinct pour gérer la consommation de mémoire de 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 elle n’a aucun effet. Vous pouvez lier une base de données à un pool de ressources nommé si, à l’avenir, vous souhaiterez peut-être créer des tables mémoire optimisées dans la base de données.

Avant de pouvoir lier une base de données à un pool de ressources, la base de données et le pool de ressources doivent exister. La liaison prend effet la prochaine fois que la base de données est mise en ligne. Pour plus d’informations, consultez États de base de données .

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

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. Ce qui importe, c’est qu’ils existent tous les deux avant de lier la base de données au pool de ressources.

Créer la base de données

La Transact-SQL suivante crée une base de données nommée IMOLTP_DB qui contiendra une ou plusieurs tables optimisées en mémoire. Le chemin d’accès <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 de MIN_MEMORY_PERCENT et de MAX_MEMORY_PERCENT

Une fois que vous avez déterminé les besoins en mémoire de vos tables optimisées en mémoire, 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 une valeur supérieure.

Exemple :
Pour cet exemple, nous partons du principe que, à partir de vos calculs, vous avez déterminé que vos tables et index optimisés en mémoire ont besoin de 16 Go de mémoire. Supposons que vous avez 32 Go de mémoire allouée à votre disposition.

À première vue, il peut sembler que vous devez définir MIN_MEMORY_PERCENT et MAX_MEMORY_PERCENT à 50 (16 est 50% de 32). Toutefois, cela ne donnerait pas à vos tables optimisées en mémoire suffisamment de mémoire. En examinant le tableau ci-dessous (pourcentage de mémoire disponible pour les tables et index mémoire optimisées), nous constatons qu’en présence de 32 Go de mémoire validée, seuls 80% de ceux-ci sont disponibles pour les tables et les index à mémoire optimisée. Par conséquent, nous calculons les pourcentages min et max en fonction de la mémoire disponible, et non de la mémoire validée.

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

Remplacer par des nombres réels :
percentNeeded = 16 / (32 * 0.8) = 16 / 25.6 = 0.625

Vous avez donc besoin d’au moins 62,5% de la mémoire disponible pour répondre aux besoins de 16 Go de vos tables et index optimisés en mémoire. Étant donné que les valeurs de MIN_MEMORY_PERCENT et de MAX_MEMORY_PERCENT doivent être des entiers, nous les définissons sur au moins 63%.

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

Lors de la configuration de la mémoire pour les tables optimisées en mémoire, la planification de la capacité doit être effectuée en fonction de MIN_MEMORY_PERCENT, et non sur MAX_MEMORY_PERCENT. Consultez ALTER RESOURCE POOL (Transact-SQL) pour plus d’informations sur les MIN_MEMORY_PERCENT et les MAX_MEMORY_PERCENT. Cela fournit une disponibilité de mémoire plus prévisible pour les tables optimisées en mémoire, car MIN_MEMORY_PERCENT entraîne une pression de la mémoire sur d’autres pools de ressources pour s’assurer qu’elle est respectée. Pour vous assurer que la mémoire est disponible et éviter les conditions de mémoire insuffisante, les valeurs de MIN_MEMORY_PERCENT et de MAX_MEMORY_PERCENT doivent être identiques. Consultez Pourcentage de mémoire disponible pour les tables optimisées en mémoire et les index ci-dessous pour le pourcentage de mémoire disponible pour les tables mémoire optimisées en fonction de la quantité de mémoire validée.

Consultez les meilleures pratiques : Utilisation de In-Memory OLTP dans un environnement de machine virtuelle pour plus d’informations lors de l’utilisation d’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 pour son utilisation. Une fois le pool créé, Resource Governor est reconfiguré pour 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 sp_xtp_bind_db_resource_pool système pour lier la base de données au pool de ressources. La fonction prend deux paramètres : le nom de la base de données et le nom du pool de ressources.

La Transact-SQL suivante définit une liaison de la base de données IMOLTP_DB au pool de ressources Pool_IMOLTP. La liaison n’est pas effective tant que vous n’avez pas apporté 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_resourece_pool prend deux paramètres de chaîne : database_name et pool_name.

Confirmer la liaison

Confirmez la liaison, en notant l’ID du pool de ressources pour IMOLTP_DB. Il ne doit pas être NULL.

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

Rendre l'engagement effectif

Vous devez mettre la base de données hors connexion et revenir en ligne après la liaison au pool de ressources pour que la liaison prenne effet. Si votre base de données était liée à un autre pool précédemment, cela supprime la mémoire allouée du pool de ressources précédent et les allocations de mémoire pour votre table optimisée en mémoire et les index proviennent désormais du pool de ressources nouvellement 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  

Et 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 à la quantité de mémoire nécessaire pour vos tables optimisées en mémoire, vous devrez peut-être modifier 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 obtenir des conseils sur les valeurs à utiliser pour MIN_MEMORY_PERCENT et MAX_MEMORY_PERCENT. Pour plus d’informations, consultez la rubrique Meilleures pratiques : utilisation de In-Memory OLTP dans un environnement de machine virtuelle .

1. Utilisez ALTER RESOURCE POOL pour modifier la valeur de MIN_MEMORY_PERCENT et MAX_MEMORY_PERCENT.

2. Utilisez ALTER RESURCE GOVERNOR pour reconfigurer le gouverneur de ressources 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 les index à mémoire optimisée

Si vous mappez une base de données avec des tables optimisées en mémoire et une charge de travail SQL Server au même pool de ressources, Resource Governor définit un seuil interne pour In-Memory l’utilisation OLTP afin que les utilisateurs du pool n’aient pas de conflits sur l’utilisation du pool. En règle générale, le seuil d’utilisation de In-Memory OLTP est d’environ 80% du pool. Le tableau suivant présente les seuils réels pour différentes tailles de mémoire.

Lorsque vous créez un pool de ressources dédié pour la base de données OLTP In-Memory, vous devez estimer la quantité de mémoire physique dont vous avez besoin pour les tables en mémoire après avoir comptabilisé les versions de lignes et la croissance des données. Une fois que vous avez estimé la mémoire nécessaire, vous créez un pool de ressources avec un pourcentage de la mémoire cible de validation pour l’instance SQL, comme indiqué par la colonne « committed_target_kb » dans la vue sys.dm_os_sys_info dynamique (voir sys.dm_os_sys_info). Par exemple, vous pouvez créer un pool de ressources P1 avec 40% de la mémoire totale disponible pour l’instance. Sur ces 40%, le moteur OLTP In-Memory reçoit un pourcentage plus petit pour stocker les données OLTP In-Memory. Cela permet de s’assurer que In-Memory OLTP ne consomme pas toutes les mémoires de ce pool. Cette valeur de la plus petite valeur du pourcentage dépend de la mémoire allouée cible. Le tableau suivant décrit la mémoire disponible pour In-Memory base de données OLTP dans un pool de ressources (nommé ou par défaut) avant qu’une erreur OOM soit générée.

Par exemple, si votre « mémoire validée cible » est de 100 Go et que vous estimez que vos tables et index optimisés en mémoire ont besoin de 60GBof mémoire, vous pouvez ensuite créer un pool de ressources avec MAX_MEMORY_PERCENT = 67 (60 Go nécessaires / 0,90 = 66,667 Go - arrondir jusqu’à 67 Go ; 67 Go / 100 Go installés = 67%) pour vous assurer que vos objets OLTP In-Memory ont les 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 entre 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  

Cet exemple de sortie montre que la mémoire prise par les objets à mémoire optimisée 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 qui peut être prise par les objets optimisés en mémoire système et utilisateur mappés à ce pool.

Exemple de sortie
Cette sortie provient de la base de données et des tables que nous avons créées ci-dessus.

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         PoolIMOLTP 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 optimisées en mémoire à l’aide du DMV sys.dm_resource_governor_resource_pools avec précision pour la base de données d’intérêt.

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