Partager via


Surveiller et dépanner l'utilisation de la mémoire avec l'OLTP en mémoire

S’applique à : SQL Server

OLTP en mémoire utilise la mémoire selon des modèles différents pour les tables sur disque. Analysez la quantité de mémoire allouée et utilisée par les tables et les index optimisés en mémoire dans votre base de données à l'aide des DMV ou des compteurs de performance fournis pour la mémoire et le sous-système de garbage collection. Vous aurez ainsi la visibilité au niveau du système et de la base de données, et vous pourrez prévenir les problèmes liés à l'insuffisance de mémoire.

Cet article traite de la surveillance de votre utilisation de la mémoire de l'OLTP en mémoire pour SQL Server.

Remarque

Ce tutoriel ne s'applique pas à Azure SQL Managed Instance ni à Azure SQL Database. Pour une démonstration de l'OLTP en mémoire dans Azure SQL, consultez plutôt :

Pour plus d'informations sur la surveillance de l'utilisation de l'OLTP en mémoire, consultez :

1. Créer une base de données d'exemple avec des tables à mémoire optimisée

Les étapes suivantes permettent de créer une base de données utilisable pour notre exercice.

  1. Lancez SQL Server Management Studio.

  2. Sélectionnez Nouvelle requête.

    Remarque

    Ignorez cette prochaine étape si vous avez déjà une base de données avec des tables optimisées en mémoire.

  3. Collez ce code dans la nouvelle fenêtre de requête et exécutez chaque section pour créer la base de données de test pour cet exercice : IMOLTP_DB.

    -- create a database to be used  
    CREATE DATABASE IMOLTP_DB  
    GO
    
  4. L'exemple de script ci-dessous utilise C:\Data, mais votre instance utilise probablement différents emplacements de dossiers pour les fichiers de données de base de données. Mettez à jour le script suivant pour utiliser un emplacement approprié pour le fichier en mémoire, puis exécutez-le.

    ALTER DATABASE IMOLTP_DB ADD FILEGROUP IMOLTP_DB_xtp_fg CONTAINS MEMORY_OPTIMIZED_DATA  
    ALTER DATABASE IMOLTP_DB ADD FILE( NAME = 'IMOLTP_DB_xtp' , FILENAME = 'C:\Data\IMOLTP_DB_xtp') TO FILEGROUP IMOLTP_DB_xtp_fg;  
    GO
    
  5. Le script suivant crée trois tables à mémoire optimisée que vous pouvez utiliser dans la suite de cette rubrique. Dans l'exemple, nous avons mappé la base de données à un pool de ressources de sorte à pouvoir contrôler la quantité de mémoire consommée par les tables optimisées en mémoire. Exécutez l'instruction suivante dans la base de données IMOLTP_DB.

    -- create some tables  
    USE IMOLTP_DB  
    GO  
    
    -- create the resoure pool  
    CREATE RESOURCE POOL PoolIMOLTP WITH (MAX_MEMORY_PERCENT = 60);  
    ALTER RESOURCE GOVERNOR RECONFIGURE;  
    GO  
    
    -- bind the database to a resource pool  
    EXEC sp_xtp_bind_db_resource_pool 'IMOLTP_DB', 'PoolIMOLTP'  
    
    -- you can query the binding using the catalog view as described here  
    SELECT d.database_id  
         , d.name  
         , d.resource_pool_id  
    FROM sys.databases d  
    GO  
    
    -- take database offline/online to finalize the binding to the resource pool  
    USE master  
    GO  
    
    ALTER DATABASE IMOLTP_DB SET OFFLINE  
    GO  
    ALTER DATABASE IMOLTP_DB SET ONLINE  
    GO  
    
    -- create some tables  
    USE IMOLTP_DB  
    GO  
    
    -- create table t1  
    CREATE TABLE dbo.t1 (  
           c1 int NOT NULL CONSTRAINT [pk_t1_c1] PRIMARY KEY NONCLUSTERED  
         , c2 char(40) NOT NULL  
         , c3 char(8000) NOT NULL  
         ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
    GO  
    
    -- load t1 150K rows  
    DECLARE @i int = 0  
    BEGIN TRAN  
    WHILE (@i <= 150000)  
       BEGIN  
          INSERT t1 VALUES (@i, 'a', replicate ('b', 8000))  
          SET @i += 1;  
       END  
    Commit  
    GO  
    
    -- Create another table, t2  
    CREATE TABLE dbo.t2 (  
           c1 int NOT NULL CONSTRAINT [pk_t2_c1] PRIMARY KEY NONCLUSTERED  
         , c2 char(40) NOT NULL  
         , c3 char(8000) NOT NULL  
         ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
    GO  
    
    -- Create another table, t3   
    CREATE TABLE dbo.t3 (  
           c1 int NOT NULL CONSTRAINT [pk_t3_c1] PRIMARY KEY NONCLUSTERED HASH (c1) WITH (BUCKET_COUNT = 1000000)  
         , c2 char(40) NOT NULL  
         , c3 char(8000) NOT NULL  
         ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)  
    GO  
    

2. Surveiller l'utilisation de la mémoire

Surveiller l'utilisation de la mémoire avec SQL Server Management Studio

Depuis SQL Server 2014 (12.x), SQL Server Management Studio dispose de rapports standard intégrés pour surveiller la mémoire consommée par les tables en mémoire. Vous pouvez accéder à ces rapports à l'aide de l'Explorateur d'objets. Vous pouvez également utiliser l'Explorateur d'objets pour surveiller la mémoire consommée par les tables optimisées en mémoire individuelles.

Consommation au niveau de la base de données

Surveillez l'utilisation de la mémoire au niveau de la base de données comme suit.

  1. Lancez SQL Server Management Studio et connectez-vous à votre SQL Server ou SQL Managed Instance.

  2. Dans l'Explorateur d'objets, cliquez avec le bouton droit sur la base de données que vous souhaitez surveiller.

  3. Dans le menu local, sélectionnez Rapports –>Rapports standard –>Utilisation de la mémoire par les objets à mémoire optimisée

Capture d’écran montrant l’Explorateur d’objets avec l’option Rapports > Rapports standard > Utilisation de la mémoire par les objets à mémoire optimisée sélectionnée.

Ce rapport affiche la consommation de la mémoire par la base de données créée précédemment.

Capture d’écran du rapport Utilisation totale de la mémoire par les objets mémoire optimisés.

Surveiller l'utilisation de la mémoire avec des DMV

De nombreuses vues de gestion dynamiques sont disponibles pour surveiller la mémoire consommée par les tables, index et objets système à mémoire optimisée, et par les structures d’exécution.

Consommation de mémoire par les tables et les index optimisés en mémoire

Déterminez la quantité de mémoire pour toutes les tables utilisateur, index et objets système en interrogeant sys.dm_db_xtp_table_memory_stats comme indiqué ici.

SELECT object_name(object_id) AS [Name]
     , *  
   FROM sys.dm_db_xtp_table_memory_stats;

Exemple de sortie

Name       object_id   memory_allocated_for_table_kb memory_used_by_table_kb memory_allocated_for_indexes_kb memory_used_by_indexes_kb  
---------- ----------- ----------------------------- ----------------------- ------------------------------- -------------------------  
t3         629577281   0                             0                       128                             0  
t1         565577053   1372928                       1200008                 7872                            1942  
t2         597577167   0                             0                       128                             0  
NULL       -6          0                             0                       2                               2  
NULL       -5          0                             0                       24                              24  
NULL       -4          0                             0                       2                               2  
NULL       -3          0                             0                       2                               2  
NULL       -2          192                           25                      16                              16  

Pour plus d’informations, consultez sys.dm_db_xtp_table_memory_stats.

Consommation de mémoire par les structures internes du système

La mémoire est également consommée par les objets système, comme les structures transactionnelles, les mémoires tampons pour les fichiers de données et delta, les structures de garbage collection, etc. Déterminez la mémoire utilisée pour ces objets système en interrogeant sys.dm_xtp_system_memory_consumers comme indiqué ici.

SELECT memory_consumer_desc  
     , allocated_bytes/1024 AS allocated_bytes_kb  
     , used_bytes/1024 AS used_bytes_kb  
     , allocation_count  
   FROM sys.dm_xtp_system_memory_consumers  

Exemple de sortie

memory_consumer_ desc allocated_bytes_kb   used_bytes_kb        allocation_count  
------------------------- -------------------- -------------------- ----------------  
VARHEAP                   0                    0                    0  
VARHEAP                   384                  0                    0  
DBG_GC_OUTSTANDING_T      64                   64                   910  
ACTIVE_TX_MAP_LOOKAS      0                    0                    0  
RECOVERY_TABLE_CACHE      0                    0                    0  
RECENTLY_USED_ROWS_L      192                  192                  261  
RANGE_CURSOR_LOOKSID      0                    0                    0  
HASH_CURSOR_LOOKASID      128                  128                  455  
SAVEPOINT_LOOKASIDE       0                    0                    0  
PARTIAL_INSERT_SET_L      192                  192                  351  
CONSTRAINT_SET_LOOKA      192                  192                  646  
SAVEPOINT_SET_LOOKAS      0                    0                    0  
WRITE_SET_LOOKASIDE       192                  192                  183  
SCAN_SET_LOOKASIDE        64                   64                   31  
READ_SET_LOOKASIDE        0                    0                    0  
TRANSACTION_LOOKASID      448                  448                  156  
PGPOOL:256K               768                  768                  3  
PGPOOL: 64K               0                    0                    0  
PGPOOL:  4K               0                    0                    0  

Pour plus d'informations, consultez sys.dm_xtp_system_memory_consumers.

Consommation de mémoire à l'exécution lors de l'accès aux tables optimisées en mémoire

Déterminez la mémoire consommée par les structures au moment de l'exécution, notamment le cache de procédures, avec la requête suivante. Exécutez cette requête pour obtenir la mémoire utilisée par les structures d'exécution comme le cache de procédures. Toutes les structures d'exécution ont des balises XTP.

SELECT memory_object_address  
     , pages_in_bytes  
     , bytes_used  
     , type  
   FROM sys.dm_os_memory_objects WHERE type LIKE '%xtp%'  

Exemple de sortie

memory_object_address pages_ in_bytes bytes_used type  
--------------------- ------------------- ---------- ----  
0x00000001F1EA8040    507904              NULL       MEMOBJ_XTPDB  
0x00000001F1EAA040    68337664            NULL       MEMOBJ_XTPDB  
0x00000001FD67A040    16384               NULL       MEMOBJ_XTPPROCCACHE  
0x00000001FD68C040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD284040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD302040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD382040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD402040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD482040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD502040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001FD67E040    16384               NULL       MEMOBJ_XTPPROCPARTITIONEDHEAP  
0x00000001F813C040    8192                NULL       MEMOBJ_XTPBLOCKALLOC  
0x00000001F813E040    16842752            NULL       MEMOBJ_XTPBLOCKALLOC  

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

Mémoire consommée par le moteur OLTP en mémoire sur l'instance

La mémoire allouée au moteur OLTP en mémoire et aux objets à mémoire optimisée est gérée de la même façon que pour tout autre consommateur de mémoire dans l'instance SQL Server. Les régisseurs de mémoire de type MEMORYCLERK_XTP tiennent compte de toute la mémoire allouée au moteur OLTP en mémoire. Utilisez la requête suivante pour rechercher toute la mémoire utilisée par le moteur OLTP en mémoire.

-- This DMV accounts for all memory used by the in-memory engine  
SELECT type  
   , name  
   , memory_node_id  
   , pages_kb/1024 AS pages_MB   
   FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'  

L'exemple de sortie montre que la mémoire allouée est constituée de 18 Mo de mémoire au niveau du système et de 1 358 Mo alloués à la base de données ayant database_id = 5. Puisque cette base de données est mappée à un pool de ressources dédié, cette mémoire est comptabilisée dans ce pool de ressources.

type                 name       memory_node_id pages_MB  
-------------------- ---------- -------------- --------------------  
MEMORYCLERK_XTP      Default    0              18  
MEMORYCLERK_XTP      DB_ID_5    0              1358  
MEMORYCLERK_XTP      Default    64             0  

Pour plus d’informations, consultez sys.dm_os_memory_clerks.

3. Gérer la mémoire consommée par les objets à mémoire optimisée

Vous pouvez contrôler la mémoire totale consommée par les tables à mémoire optimisée en la liant à une liste de ressources partagées nommée. Pour plus d'informations, consultez Lier une base de données avec des tables à mémoire optimisée à une liste de ressources partagées.

Résoudre les problèmes de mémoire

La résolution des problèmes de mémoire comporte trois étapes :

  1. Identifiez la quantité de mémoire consommée par les objets dans votre base de données ou dans votre instance. Utilisez les nombreux outils d'analyse disponibles pour les tables optimisées en mémoire décrits précédemment. Par exemple, consultez les exemples de requêtes sur les DMV sys.dm_db_xtp_table_memory_stats ou sys.dm_os_memory_clerks.

  2. Déterminez la façon dont la consommation de mémoire augmente et de quelle marge vous disposez. En surveillant régulièrement la consommation de mémoire, vous saurez de quelle façon l'utilisation de la mémoire augmente. Par exemple, si vous avez mappé la base de données à un pool de ressources nommé, surveillez le compteur de performance Used Memory (KB) pour voir comme l'utilisation de la mémoire augmente.

  3. Agissez pour atténuer les éventuels problèmes de mémoire. Pour plus d’informations, consultez Résoudre les problèmes de mémoire insuffisante.