Monitorizar e resolver problemas de utilização de memória com OLTP na memória

Aplica-se a: SQL Server

In-Memory OLTP consome memória em padrões diferentes das tabelas baseadas em disco. Pode monitorizar a quantidade de memória alocada e utilizada por tabelas e índices otimizados para memória da sua base de dados, utilizando os DMVs ou contadores de desempenho relativos à memória e ao subsistema de recolha de lixo. Isto dá-lhe visibilidade tanto ao nível do sistema como da base de dados e permite evitar problemas devido à exaustão de memória.

Este artigo aborda a monitorização da utilização de memória do In-Memory OLTP no SQL Server.

1. Criar uma base de dados de exemplo com tabelas otimizadas para memória

Os passos seguintes criam uma base de dados para usar no nosso exercício.

1. Inicie o SQL Server Management Studio.

2. Selecione Nova consulta.

   Note

   Pode saltar este próximo passo se já tiver uma base de dados com tabelas otimizadas para memória.

3. Cole este código na nova janela de consulta e execute cada secção para criar a base de dados de teste para este exercício, IMOLTP_DB.

   -- create a database to be used  
   CREATE DATABASE IMOLTP_DB  
   GO
   

4. O script de exemplo abaixo usa C:\Data, mas a tua instância provavelmente usa diferentes localizações de pastas para ficheiros de dados da base de dados. Atualize o script seguinte para usar a localização correta do ficheiro em memória e execute.

   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. O script seguinte criará três tabelas otimizadas para memória que poderá usar no restante deste tópico. No exemplo, mapeámos a base de dados para um pool de recursos para podermos controlar quanta memória pode ser ocupada por tabelas otimizadas para memória. Execute o seguinte script na IMOLTP_DB base de dados.

   -- create some tables  
   USE IMOLTP_DB  
   GO  
   
   -- create the resource 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. Monitorizar o uso de memória

Monitorizar a utilização da memória com o SQL Server Management Studio

Desde o SQL Server 2014 (12.x), o SQL Server Management Studio tem relatórios padrão incorporados para monitorizar a memória consumida pelas tabelas em memória. Pode aceder a estes relatórios usando o Object Explorer. Também pode usar o explorador de objetos para monitorizar a memória consumida por tabelas individuais otimizadas para memória.

Consumo ao nível da base de dados

Pode monitorizar a utilização de memória ao nível da base de dados da seguinte forma.

1. Inicie o SQL Server Management Studio e ligue-se ao seu SQL Server ou instância gerida SQL.

2. No Object Explorer, clique com o botão direito na base de dados onde quer relatórios.

3. No menu de contexto, selecionar, Relatórios ->Relatórios Padrão ->Utilização de Memória por Objetos Otimizados para a Memória

Este relatório mostra o consumo de memória pela base de dados que criámos acima.

Monitorize a utilização de memória com DMVs

Há muitas DMVs disponíveis para monitorizar a memória consumida por tabelas otimizadas para memória, índices, objetos do sistema e estruturas de tempo de execução.

Consumo de memória por tabelas e índices otimizados para memória

Pode encontrar o consumo de memória de todas as tabelas de utilizador, índices e objetos do sistema consultando sys.dm_db_xtp_table_memory_stats , como mostrado aqui.

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

Resultado da Amostra

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  

Para mais informações, consulte sys.dm_db_xtp_table_memory_stats.

Consumo de memória pelas estruturas internas do sistema

A memória é também consumida por objetos do sistema, como estruturas transacionais, buffers para dados e ficheiros delta, estruturas de recolha de lixo e mais. Pode encontrar a memória usada para estes objetos do sistema consultando sys.dm_xtp_system_memory_consumers , como mostrado aqui.

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  

Resultado da Amostra

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  

Para mais informações, consulte sys.dm_xtp_system_memory_consumers).

Consumo de memória em tempo de execução ao aceder a tabelas otimizadas para memória

Pode determinar a memória consumida pelas estruturas de tempo de execução, como a cache de procedimentos, com a seguinte consulta: execute esta consulta para obter a memória usada por estruturas de tempo de execução, como para a cache de procedimentos. Todas as estruturas em tempo de execução estão etiquetadas com XTP.

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

Resultado da Amostra

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  

Para mais informações, consulte sys.dm_os_memory_objects (Transact-SQL).

Memória consumida pelo motor OLTP em memória em toda a instância

A memória atribuída ao motor OLTP In-Memory e aos objetos otimizados para memória é gerida da mesma forma que qualquer outro consumidor de memória dentro de uma instância SQL Server. Os memory clerks do tipo MEMORYCLERK_XTP contabilizam toda a memória alocada para o motor OLTP In-Memory. Use a seguinte consulta para encontrar toda a memória utilizada pelo motor OLTP In-Memory.

-- 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%'  

O exemplo de saída seguinte mostra que a memória alocada é de 18 MB de memória de sistema e 1358 MB atribuídos a database_id = 5. Como esta base de dados está mapeada para um pool de recursos dedicado, essa memória é contabilizada nesse pool de recursos.

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

Para obter mais informações, consulte sys.dm_os_memory_clerks.

3. Gerir a memória consumida por objetos otimizados para memória

Pode controlar a memória total consumida por tabelas otimizadas para memória ligando-a a um pool de recursos nomeado. Para mais informações, consulte Vincular uma base de dados com tabelas otimizadas para memória a um pool de recursos.

Solucionar problemas de memória

A resolução de problemas de memória é um processo em três etapas:

1. Identifique quanta memória está a ser consumida pelos objetos na sua base de dados ou instância. Pode usar um conjunto completo de ferramentas de monitorização disponíveis para tabelas otimizadas para memória, conforme descrito anteriormente. Por exemplo, veja as consultas de exemplo nos DMVs sys.dm_db_xtp_table_memory_stats ou sys.dm_os_memory_clerks.

2. Determine como o consumo de memória está a aumentar e quanta margem ainda tem disponível. Ao monitorizar periodicamente o consumo de memória, pode saber como o consumo de memória está a crescer. Por exemplo, se mapeou a base de dados para um pool de recursos nomeado, pode monitorizar o contador de desempenho Memória Usada (KB) para ver como o uso de memória está a crescer.

3. Tome medidas para mitigar os potenciais problemas de memória. Para mais informações, consulte Resolver Problemas de Falta de Memória.

Conteúdo relacionado

• vincular um banco de dados com tabelas Memory-Optimized a um pool de recursos
• Alterar MIN_MEMORY_PERCENT e MAX_MEMORY_PERCENT em um pool existente