Monitorar e solucionar problemas de uso de memória

O SQL Server In-Memory OLTP consome memória em padrões diferentes das tabelas baseadas em disco. Você pode monitorar a quantidade de memória alocada e usada por tabelas e índices otimizados para memória em seu banco de dados usando os DMVs ou contadores de desempenho fornecidos para a memória e para o subsistema de coleta de lixo. Isso oferece visibilidade no nível do sistema e do banco de dados e permite evitar problemas devido ao esgotamento da memória.

Este tópico aborda o monitoramento do uso de memória OLTP In-Memory.

Criar um banco de dados de exemplo com tabelas com otimização de memória

Você pode ignorar esta seção se já tiver um banco de dados com tabelas com otimização de memória.

As etapas a seguir criam um banco de dados com 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 para que possamos controlar a quantidade de memória que pode ser obtida por tabelas com otimização de memória.

Inicialização do SQL Server Management Studio.
Clicar em Nova Consulta.

Cole esse código na nova janela de consulta e execute cada seção.

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

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

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

Monitorando o uso de memória

Como usar o SQL Server Management Studio.

O SQL Server 2014 é fornecido com 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 no nível do banco de dados

Você pode monitorar o uso de memória no nível do banco de dados da seguinte maneira.

Inicie o SQL Server Management Studio e conecte-se a um servidor.
No Pesquisador de Objetos, clique com o botão direito do mouse no banco de dados no qual você deseja obter relatórios.
Na seleção do menu de contexto, Relatórios ->Relatórios Padrão ->Uso de Memória por Objetos Otimizados para Memória

HK_MM_SSMS

Este relatório mostra o consumo de memória pelo banco de dados que criamos acima.

HK_MM_SSMS

Usando DMVs

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

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

Você pode encontrar 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 como 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, consulte 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, como estruturas transacionais, buffers para arquivos delta e de dados, estruturas de coleta de lixo e muito mais. Você pode encontrar a memória usada para esses 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

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 , consulte sys.dm_xtp_system_memory_consumers (Transact-SQL).

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 de tempo de execução, como o cache de procedimentos com a seguinte consulta: execute essa consulta para obter a memória usada por estruturas de tempo de execução, como para o cache de procedimentos. Todas as estruturas de 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, consulte sys.dm_os_memory_objects (Transact-SQL).

Memória consumida pelo mecanismo OLTP In-Memory em toda a instância

A memória alocada para o mecanismo OLTP In-Memory e os objetos com otimização de memória são gerenciados da mesma forma que qualquer outro consumidor de memória em uma instância do SQL Server. Os controladores do tipo MEMORYCLERK_XTP são responsáveis por toda a memória alocada para o motor OLTP In-Memory. Use a consulta a seguir para localizar toda a memória usada pelo mecanismo OLTP In-Memory.

-- this DMV accounts for all memory used by the hek_2 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 o total de memória alocada é de 18 MB de memória consumida no nível do sistema e 1358 MB alocados para o identificador de banco de dados 5. Como esse banco de dados é mapeado para um pool de recursos dedicado, essa memória é contabilizada nesse pool de recursos.

Saída de exemplo

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 (Transact-SQL).

Gerenciamento de 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 associando-a a um pool de recursos nomeado, conforme descrito no tópico Associar um banco de dados com tabelas Memory-Optimized a um pool de recursos.

Solução de problemas de memória

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

Identificar a quantidade de memória que está sendo consumido pelos objetos no banco de dados ou instância. Você pode usar um conjunto avançado de ferramentas de monitoramento disponíveis para tabelas com otimização de memória, conforme descrito anteriormente. Por exemplo, as DMVs sys.dm_db_xtp_table_memory_stats ou sys.dm_os_memory_clerks.
Determine como o consumo de memória está crescendo e quanto espaço na cabeça você ainda tem. Monitorando o consumo de memória periodicamente, você pode saber como o uso da memória está crescendo. 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 como o uso de memória está crescendo.
Tome medidas para atenuar os possíveis problemas de memória. Para obter mais informações, consulte Resolver problemas de memória insuficiente.

Consulte Também

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

Last updated on 2017-06-13

Compartilhar via

Monitorar e solucionar problemas de uso de memória

Criar um banco de dados de exemplo com tabelas com otimização de memória

Monitorando o uso de memória

Como usar o SQL Server Management Studio.

Consumo no nível do banco de dados

Usando DMVs

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

Consumo de memória por estruturas internas do sistema

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

Memória consumida pelo mecanismo OLTP In-Memory em toda a instância

Gerenciamento de memória consumida por objetos com otimização de memória

Solução de problemas de memória

Consulte Também

Recursos adicionais