Monitorar e solucionar problemas de uso de memória com o OLTP in-memory

  • Artigo

Aplica-se a:SQL Server

O OLTP in-memory consome memória em padrões diferentes em comparação com tabelas baseadas em disco. Você pode monitorar a quantidade de memória alocada e usada pelas tabelas e índices com otimização de memória em seu banco de dados, usando as DMVs ou os contadores de desempenho fornecidos para a memória e o subsistema de coleta de lixo. Isso oferece visibilidade em nível de sistema e banco de dados, e permite que você evite problemas devido ao esgotamento de memória.

Este artigo abrange o monitoramento do uso de memória do OLTP in-memory para o SQL Server.

Observação

Este tutorial não se aplica à Instância Gerenciada de SQL do Azure ou ao Banco de Dados SQL do Azure. Em vez disso, para obter uma demonstração do OLTP in-memory no SQL do Azure, consulte:

Para obter mais informações sobre o monitoramento do uso do OLTP in-memory, consulte:

1. Criar um banco de dados de amostra com tabelas com otimização de memória

As etapas a seguir criam um banco de dados para nosso exercício.

  1. Inicialização do SQL Server Management Studio.

  2. Selecione Nova Consulta.

    Observação

    Você poderá ignorar a próxima etapa se já tiver um banco de dados com tabelas com otimização de memória.

  3. Cole esse código na nova janela de consulta e execute cada seção para criar o banco 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 amostra abaixo usa C:\Data, mas sua instância provavelmente usa locais de pasta diferentes para arquivos de dados de banco de dados. Atualize o script a seguir para usar um local apropriado para o local do arquivo na 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 a seguir vai criar três tabelas com otimização de memória que você pode usar no restante deste tópico. No exemplo, mapeamos o banco de dados para um pool de recursos a fim de controlar a quantidade de memória que pode ser usada por tabelas com otimização de memória. Execute o seguinte script no banco de dados 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. Monitorar o uso de memória

Monitorar o uso de 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 internos para monitorar a memória consumida por tabelas na memória. Você pode acessar esses relatórios usando o Pesquisador de Objetos. Você também pode usar o Pesquisador de Objetos para monitorar a memória consumida por tabelas individuais com otimização de memória.

Consumo em nível de banco de dados

Você pode monitorar o uso da memória em nível de banco de dados da forma a seguir.

  1. Inicie o SQL Server Management Studio e conecte-se ao SQL Server ou Instância Gerenciada de SQL.

  2. No Pesquisador de Objetos, clique com o botão direito do mouse no banco de dados sobre o qual você deseja obter relatórios.

  3. No menu de contexto, escolha Relatórios ->Relatórios Padrão ->Uso de Memória por Objetos Otimizados para Memória

Screenshot showing the Object Explorer with Reports > Standard Reports > Memory Usage By Memory Optimized Objects selected.

Esse relatório mostra o consumo de memória pelo banco de dados criado anteriormente.

Screenshot of the Total Memory Usage By Memory Optimized Objects report.

Monitorar o uso de memória com DMVs

Há várias DMVs disponíveis para monitorar a memória consumida por tabelas com otimização de memória, índices, objetos do sistema e estruturas de tempo de execução.

Consumo de memória por tabelas com otimização de memória e índices

Você pode localizar o consumo de memória para todas as tabelas de usuário, índices e objetos do sistema consultando sys.dm_db_xtp_table_memory_stats , conforme mostrado aqui.

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

Saída de exemplo

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 obter mais informações, confira sys.dm_db_xtp_table_memory_stats.

Consumo de memória por estruturas internas do sistema

A memória também é consumida por objetos do sistema, tais como estruturas transacionais, buffers para arquivos delta e de dados, estruturas de coleta de lixo, entre outros. Você pode localizar a memória usada para esses objetos de sistema consultando sys.dm_xtp_system_memory_consumers , conforme 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

Saída de exemplo

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 obter mais informações, confira sys.dm_xtp_system_memory_consumers).

Consumo de memória em tempo de execução ao acessar tabelas com otimização de memória

Você pode determinar a memória consumida por estruturas em tempo de execução; por exemplo, o cache de procedimento com a seguinte consulta: execute esta consulta para obter a memória usada por estruturas em tempo de execução, como o cache de procedimento. Todas as estruturas em tempo de execução são marcadas com XTP.

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

Saída de exemplo

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 obter mais informações, confira sys.dm_os_memory_objects (Transact-SQL).

Memória consumida pelo mecanismo do OLTP in-memory na instância

A memória alocada para o mecanismo do OLTP in-memory e os objetos com otimização de memória são gerenciados da mesma maneira que qualquer outro consumidor de memória em uma instância do SQL Server. Os administradores do tipo MEMORYCLERK_XTP são responsáveis por toda a memória alocada para o mecanismo do OLTP in-memory. Use a consulta a seguir para localizar toda a memória usada pelo mecanismo do 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%'

A saída de exemplo mostra que a memória alocada equivale a 18 MB de memória no nível de sistema e a 1.358 MB alocados para database_id = 5. Como esse banco de dados é mapeado para um pool de recursos dedicados, 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, confira sys.dm_os_memory_clerks.

3. Gerenciar a memória consumida por objetos com otimização de memória

Você pode controlar a memória total consumida por tabelas com otimização de memória vinculando-a a um pool de recursos nomeado. Para obter mais informações, confira Associar um banco de dados com tabelas com otimização de memória a um pool de recursos.

Solucionar problemas com memória

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

  1. Identificar a quantidade de memória que está sendo consumido pelos objetos no banco de dados ou instância. Você pode usar um excelente conjunto de ferramentas de monitoramento disponíveis para tabelas com otimização de memória, conforme descrito anteriormente. Por exemplo, consulte as consultas de amostra nos DMVs sys.dm_db_xtp_table_memory_stats ou sys.dm_os_memory_clerks.

  2. Determine como o consumo de memória aumenta e o espaço disponível para você. Ao monitorar o consumo de memória periodicamente, você pode saber como o uso da memória está aumentando. Por exemplo, se você mapeou o banco de dados para um pool de recursos nomeado, poderá monitorar o contador de desempenho Memória Usada (KB) para ver o aumento no uso de memória.

  3. Execute uma ação para reduzir os problemas potenciais de memória. Para obter mais informações, confira Resolver problemas de memória insuficiente.

Conteúdo relacionado